シート上のボタン、オブジェクト名取得 or 変更

質問

探してみたのですが、
良い方法が見当たらないため質問させて頂きます。
Excel2003を使用しています。

UserForm上で、シートにボタンを追加し、
マクロを登録するプログラムを組みました。

--------------------------------------

With ActiveSheet.Buttons.Add(Range("H1").Left, _
Range("H1").Top, _
Range("H1：I1").Width, _
Range("H1").Height)

.OnAction = "HiddenDataOpenMacro"
.Characters.Text = "表示"
.Characters.Font.Size = 8
End With

---------------------------------------

この場合、H1の部分に新しいボタンが登録されます。
作成したボタンのオブジェクト名が知りたいのですが、
どのようにしたら分かるでしょうか？

もしくは、HiddenDataOpenMacro上に
どういうプログラムを組めばいいのか教えて頂きたいです。

---------------------------------------
Sub HiddenDataOpenMacro()
If ActiveSheet.Commandbuton1.Caption = "表示" Then
ActiveSheet.CommandButton1.Caption = "非表示"
ActiveSheet.Rows.Hidden = False

ElseIf ActiveSheet.Commandbuton1.Caption = "非表示" Then
ActiveSheet.CommandButton1.Caption = "表示"
ActiveSheet.Rows("2:20").Hidden = True

Else
MsgBox "違うよ！"

End If
End Sub
------------------------------------------

ためしに、上記マクロを考えてみました。
シート上には、マクロ登録してあるボタンがもう１つあります。

やりたいことはシンプルでして、
ボタンに表示されている文字列が”表示”だった場合、
ボタンに表示されている文字列を”非表示”に変更
また、非表示にしていたデータを表示にします。

ボタンの文字列が”非表示”だった場合には、
文字列を”表示”に変更し、
指定部分を非表示にします。

標準モジュールでの操作ではできませんでした。
（メソッドをサポートしてませんとエラーが出ます。）

シートは毎回新しく作り直すため、
シートにプログラムを書くのはもったいない？気がします。

どうにか方法は無いでしょうか？

ボタンを作成するときに、オブジェクト名まで指定できれば
良いかと思うのですが・・・

話がごちゃごちゃになってきましたので、
やりたいことをまとめます。

●シートにある、指定文字列が書いてあるボタンのオブジェクト名を取得
●ボタン作成時にオブジェクト名を設定する方法
●If ActiveSheet.Commandbuton1.Caption = "表示" Then　ではない他の書き方

以上、よろしくお願い致します！

回答No.1

kybo · ベストアンサー

「HiddenDataOpenMacro」は以下のような感じでいいと思います。
また、シートモジュールではなく、標準モジュールに記載してください。

Sub HiddenDataOpenMacro()
With ActiveSheet.Buttons(ActiveSheet.Buttons.Count)
If .Caption = "表示" Then
.Caption = "非表示"
ElseIf .Caption = "非表示" Then
.Caption = "表示"
Else
MsgBox "違うよ！"
End If
End With
End Sub

別回で、以下のようにしてもいいです。ただしエラー等で止まるとボタンは機能しなくなります。
上の方法のほうが固いかもしれません。

’ユーザーフォームモジュールの一番上に記載
Public N As String

’ユーザーフォームに組み込む
With ActiveSheet.Buttons.Add(Range("H1").Left, _
Range("H1").Top, _
Range("H1:I1").Width, _
Range("H1").Height)
N = .Name
.OnAction = "HiddenDataOpenMacro"
.Characters.Text = "表示"
.Characters.Font.Size = 8
End With

’標準モジュール
Sub HiddenDataOpenMacro()
If ActiveSheet.Buttons(N).Caption = "表示" Then
ActiveSheet.Buttons(N).Caption = "非表示"
ElseIf ActiveSheet.Buttons(N).Caption = "非表示" Then
ActiveSheet.Buttons(N).Caption = "表示"
Else
MsgBox "違うよ！"
End If
End Sub

お礼

回答ありがとうございます！

お蔭様で思ったとおりのプログラムを組むことが出来ました。

また、２通りの回答もしてくださり
ありがとうございます！
後半に説明いただいたものは
エラーが出てしまい上手くいきませんでしたが、
上のプログラムは正常に動きました！

思い通りの結果で、本当にうれしいです！
ありがとうございました！