ピボットテーブルでクリックしたセルのラベルの取得

質問

図のようなピボットを作成しました。

C8セル(=合計/金額90円)をクリックしたときに、VBAで行ラベル、列ラベルを取得することはできますか？

イメージ
str売上月 = 関数(8, 3,”売上月”) 'C8セルの行(=8)、列(=3)と行ラベル”売上月”を引数で渡すと、"2017年1月"を取得してstr売上月に格納される
str売上日 = 関数(8, 3,”売上日”) ’←"3日"を取得する
str商品グループ = 関数(8, 3,”商品グループ”) ’←”あ”を取得する
str商品 = 関数(8, 3,”商品”) ’←”B”を取得する

よろしくお願いします。

回答No.1

real beatin（@realbeatin） · ベストアンサー

ども。目が覚めてまだ回答がないので、ザックリとですが、、、。

「C8セルをクリックしたとき」
「= 関数(8, 3,”売上月”)」
同じ設問とは思えないので、
イベントの話、関数の話、分けてお応えします。

> C8セル(=合計/金額90円)をクリックしたときに、
> VBAで行ラベル、列ラベルを取得することはできますか？

Excelは表計算アプリケーションですので、
セル選択やカーソル移動はあっても、
「セルをクリック」という概念がなく、
そのタイミングを捉えることは
（※よく誤った解説を見掛けますけれども）
出来ません。
本来は機能を呼び出したりモードを切り替える為の
「セルをダブルクリック」
で本来の機能を休止するような形でも宜しければ、
_BeforeDoubleClick イベントを試してみて下さい。
ピボットテーブルのあるシートのシートモジュールに以下。
（シートタブでシート名を右クリック→[コードの表示]→シートモジュール）
状況が判らないので、当該シートにピボットテーブルは ひとつ という条件です。
' ' // シートモジュール専用
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim oPvT As PivotTable
Dim str売上月 As String, str売上日 As String
Dim str商品グループ As String, str商品 As String
　　Set oPvT = Me.PivotTables(1)
　　If Intersect(Target, oPvT.DataBodyRange) Is Nothing Then Exit Sub
　　Cancel = True
　　On Error Resume Next
　　With Target.PivotCell
　　　　str売上月 = .RowItems(1)
　　　　str売上日 = .RowItems(2)
　　　　str商品グループ = .ColumnItems(1)
　　　　str商品 = .ColumnItems(2)
　　End With
　　On Error GoTo 0 ' 以下↓は確認のための仮のもの
　　MsgBox vbTab & "行 : " & Target.Row & vbTab & "列 : " & Target.Column _
　　　　& vbLf & "売上月 : " & str売上月 _
　　　　& vbLf & "売上日 : " & str売上日 _
　　　　& vbLf & "売上日 : " & str商品グループ _
　　　　& vbLf & "売上日 : " & str商品 _
　　　　& vbLf & "金額 : " & Target.Value
End Sub
' ' //
セル[9,3]では、売上日が無くてエラーになるとか、
エラーについては、 On Error Resume Next で逃げています。
ダブルクリックする場所（[総計]等）によっては、
本来のピボットテーブルの機能をキャンセルすることになるので、
デザイン上の追加処理をする時は、
Excelを[デザインモード]に切り替えて、
上記 Worksheet_BeforeDoubleClick を無効にしてから
編集するようにしてください。

次に、
> イメージ
> str売上月 = 関数(8, 3,”売上月”) 'C8セルの行(=8)、列(=3)と行ラベル”売上月”を引数で渡すと、"2017年1月"を取得してstr売上月に格納される
> str売上日 = 関数(8, 3,”売上日”) ’←"3日"を取得する
> str商品グループ = 関数(8, 3,”商品グループ”) ’←”あ”を取得する
> str商品 = 関数(8, 3,”商品”) ’←”B”を取得する

関数？は、こんな感じ。（VBA専用。ワークシート使用は不可です。）
ピボットテーブルのあるシートを正確なシート名で指定してください。
' ' //
Function 関数(r As Long, c As Long, f As String) As Variant
　　On Error GoTo ErrRef_
　　With Worksheets("Sheet1").Cells(r, c).PivotCell '　シート名■要指定　必須！！
　　　　Select Case f
　　　　Case "売上月": 関数 = .RowItems(1)
　　　　Case "売上日": 関数 = .RowItems(2)
　　　　Case "商品グループ": 関数 = .ColumnItems(1)
　　　　Case "商品": 関数 = .ColumnItems(2)
　　　　Case Else:: 関数 = "#REF!"
　　　　End Select
　　End With
　　On Error GoTo 0
　　Exit Function
ErrRef_:
　　関数 = "#REF!"
　　Exit Function
End Function
' ' //

関数名は[すべて置換]機能でお好きなものに替えて下さい。

基本構文は、
　Range("C8").PivotCell.RowItems(2)　→　[売上日]
のようになります。

以上ですが、次回の回答機会が未定になりますのでご了承を。

お礼

ありがとうございました。
解決しました。

ちなみに「セルをクリックした」タイミングは
Worksheet_SelectionChangeイベントを使ってます。
不勉強のため、なぜクリックしただけで変更とみなされるのかわかってませんが
とりあえず期待通りの動作をしているので使い続けてます。

回答No.2

real beatin（@realbeatin）

No.1 お礼コメントへの返信です。

> Worksheet_SelectionChangeイベントを使ってます
> とりあえず期待通りの動作をしているので使い続けてます。
それならそれでいい、と思いますが、一応説明しておきますね。

Excelでの一般的な用語として、"クリック"というと、
ボタン等のコントロールや描画オブジェクトに対して、
　マウスカーソルを当てて左クリックするとか、
　フォーカスを当ててEnterキーを押すとか、
結果として押した回数だけ関連付けした機能を呼び出すもの
という風に説明できると思います。

対して、
Excelの"Worksheet_SelectionChangeイベント"は、
文字通り「選択セル範囲 が 変更された」タイミング
を捉えて、ユーザーが求める処理を追加する為のものです。

例えば、[A1]を選択中のシートで、
[B2]範囲を選択すれば、「選択セル範囲 が 変更された」
というタイミングで、"Worksheet_SelectionChangeイベント"
が呼び出されます。
続けて、もう一度[B2]範囲を"クリック"したとしても、
選択セル範囲は変更されませんので、
"クリック"したタイミングを捉えることは出来ません。
以上は、"クリック"に比べてSelectionChangeは
不足がある、というお話。

例えば、[A1]を選択中のシートで、
[A1]セルに値を書き込んで確定する為に
　Enterキーを押すと、
　　デフォルトのExcelでは、[A2]セルに
　TABキーを押すと、
　　デフォルトのExcelでは、[B1]セルに
それぞれ、カーソル≒選択セル範囲、は遷移します。
この場合も、"クリック"していないのに
"Worksheet_SelectionChangeイベント"
が呼び出されます。
Enter、TAB、HOME、END、方向キー等のキーボード操作や、
ピボットテーブルでの、フィルター操作や折りたたみで
選択中の範囲が非表示になる時や、
この他にも、普段は意識していないタイミングで、
"クリック"していないのに
"Worksheet_SelectionChangeイベント"が呼び出される
ことは多数あります。
杓子定規に「選択セル範囲 が 変更された」かどうか、です。
以上は、"クリック"に比べてSelectionChangeは
余分がある、というお話。

【選択中のセル範囲外　の　単一セルを選択した時】
というタイミング
それ以外では、範囲選択を全くしない（させない）
という状況が作れるのであれば、
_SelectionChangeは　"クリック"イベント　のような
振る舞いをします。

昔は"セルをクリック"という不適当な言い回しを
結構見掛けたりしましたが、最近は言わないんじゃないかな？
"セルを選択する"というのが今では普通の用語ですね。

ということで、
特性を理解した上で、間違いがないように扱うのであれば、
_SelectionChangeを使うのもいいと思いますけれど、
"クリック"だと思っていると、
　必要な時に反応してくれなかったり、
　思わぬきっかけで処理が実行されたり、
ということにもなりかねませんので、
"Worksheet_SelectionChangeイベント"を
"セルをクリックしたタイミング"として説明することは、
ハッキリ間違いとして指摘しておいた方がいいだろうな、
と思ってNo.1でも触れておきましたが、
勿論、必要以上に難しく考えることもないですね。

　> Excel VBA リファレンス> オブジェクト モデル> Worksheet オブジェクト
　> Worksheet.SelectionChange イベント (Excel)
　https://msdn.microsoft.com/ja-jp/library/office/ff194470.aspx