【ExcelVBA】動的にボタン、ボタン名を生成できますか？

メニュー用のシートと、人物名のシートで構成されているブックがあります。
(例)
[メニュー] [鈴木] [佐藤] [加藤] [佐々木] [青木] [管理用]

メニューのシートには、
[鈴木] [佐藤] [加藤] [佐々木] [青木] [管理用]というコマンドボタンが設置されていて、

[鈴木] ボタンを押すと、[鈴木] シートのみ表示、他のシートは非表示
[管理用]ボタンを押すと、全シートが表示するようになってます。

人が増減すると、メニューのボタンを作り直さないといけないのですが、
ブック内の全シート名のボタンを自動的に生成するなんてできないでしょうか？

その先の課題は、
・増えた場合のマクロ
・ボタンに割り当てるマクロ
等がありますが、手動で出来る範囲なので一番手間のかかるボタン生成がまず出来たら嬉しいです。

No.7 回答者： Qchan1962 回答日時： 2022/04/08 19:01

No.5です

すみません。。。（ご覧いただいて、お解りと存じますが）
MsgBox "登録がないので管理者に連絡してください"は要りませんです
適切な個所では無いのですが、キャンセルボタン対応箇所になります
For Each ws In Worksheets　以下重複部分は不要です。。。
コピペミスです。
修正訂正が多く申し訳ありません。

No.6 回答者： fujillin 回答日時： 2022/04/08 17:10

No2です。

＞管理用というシートも存在します。
文字でシートを判断するようにしてしまったので、簡単な修正としては
　Const Manage = "管理用"
の部分を
　Const Manage = "[ 管理用 ]"
とでも変更すれば、そのままでも(一応）動作すると思います。
（シート名に[ 管理用 ]がないことが条件）

ついでながら、
＞他人のシートが見れないようにするとなると、難しそうですね。。。
ブックのオープン時には、必ず「メニュー」のシートのみ表示されるようにしておけば可能でしょう。
ただし、現状はボタンを押せば表示されてしまうので、補足にあるようにパスワードなどが必要になるかと。

＞個別にパスワードを設定したい場合だとやはり人数分のモジュールを用意する必要がありますよね？
どこかにパスワード表を作成しておけば、同じコードで処理することは可能です。
　・表をコード内に記述して、コードを非表示にしておく
　・パスワードシートを作成して、シートに非表示の保護をかけておく
等が考えられるかと。


ご質問とは関係なくなりますが、補足等の雰囲気から想像してみると・・

会社などでユーザーがきちんと管理されている環境なら、VBAでログインユーザ名を取得して、最初からその人のシートのみを表示することも可能です。
こちらの方法であれば、パスワード等も不要になるでしょうし、ユーザの操作の手間も省けると考えられます。
（非登録者の場合は、何も表示されないとかも可能）

とは言え、他の人が見られないようにしたいのなら、ブックを個人別に分けてしまった方が簡単かも知れません。
各ブックの内容を集めるのは、管理者用のブックにクエリを設定しておけばそのまま集計できますので。
（VBAを用いても良いですけれど・・）

No.5 回答者： Qchan1962 回答日時： 2022/04/08 17:07

>No.3の返信について

佐藤さんが鈴木さんのシートを見れないようにするためです。
.Visible = False　は、シートタグ右クリックの再表示で表示できるのではないかと思います。（処理をしていれば表示できないかも）
なので　.Visible = xlVeryHidden　で　設定した方が良いです。

＞個別にパスワードを設定したい場合だとやはり人数分のモジュールを用意する必要があります

Worksheets("管理用")を活用しましょう

管理用シートにユーザー名とパスワードを記録しておけば
例えば、　管理用シート　C1:C10　にユーザー名、右横のD列にパスワードを入力してある場合。

Sub Worksheet_Hidden()
Dim ws As Worksheet
Dim btn_Name As String
Dim pass As String
Dim UserName As Range
btn_Name = ActiveSheet.Buttons(Application.Caller).Text
pass = InputBox("パスワードを入力してください")
Set UserName = Worksheets("管理用").Range("C1:C10").Find(btn_Name)
If Not UserName Is Nothing Then
If UserName.Offset(, 1) <> pass Then MsgBox "パスワードが違います": Exit Sub
Else
MsgBox "登録がないので管理者に連絡してください": Exit Sub
End If
For Each ws In Worksheets
If ws.Name <> "メニュー" And ws.Name <> btn_Name Then
ws.Visible = xlVeryHidden
Else
ws.Visible = True
End If
Next
For Each ws In Worksheets
If ws.Name <> "メニュー" And ws.Name <> btn_Name Then
ws.Visible = xlVeryHidden
Else
ws.Visible = True
End If
Next
End Sub

これでこのプロシージャのみで（非表示などは）ユーザーに対応できると思います。

注意：Application.Callerを使用していますので　ボタンからの
実行以外は1004エラー　です

ちなみにアクセス時に自動でPC名やログイン名を取得して
アクセス制限や新規登録させるような方法も出来ます。
これは、機会があれば、試すのも面白いかも知れません。

No.4 回答者： Qchan1962 回答日時： 2022/04/08 15:11

＃３　失礼

Sub Worksheet_Hidden()
Dim ws As Worksheet
Dim btn_Name As String
btn_Name = ActiveSheet.Buttons(Application.Caller).Text
For Each ws In Worksheets
If ws.Name <> "メニュー" And ws.Name <> btn_Name Then
ws.Visible = xlVeryHidden
Else
ws.Visible = True
End If
Next
End Sub

Else ws.Visible = True　を　つけ忘れました

No.3 回答者： Qchan1962 回答日時： 2022/04/08 15:07

こんにちは

すでに回答があるので参考程度で
・増えた場合のマクロ
・ボタンに割り当てるマクロ
については、
＞[鈴木] ボタンを押すと、[鈴木] シートのみ表示、他のシートは非表示
のコードを以下のように
Sub Worksheet_Hidden()
Dim ws As Worksheet
Dim btn_Name As String
btn_Name = ActiveSheet.Buttons(Application.Caller).Text
For Each ws In Worksheets
If ws.Name <> "メニュー" And ws.Name <> btn_Name Then
ws.Visible = xlVeryHidden
End If
Next
End Sub
他のコードもApplication.Callerを基に作成します。

ボタン追加とマクロ登録は以下のような感じ・・
不明確な要素もあるので追加のみ
トリガーはボタンが安全ですが、Workbook_NewSheet　イベントを使う事も出来ると思います。制御系は必須

Sub sample()
Dim ws As Worksheet
Dim btn As Button
Dim n As Integer, flag As Boolean
Dim ws_name(), r As Range
ReDim ws_name(1 To Worksheets.Count)
For Each ws In Worksheets
If ws.Name <> "メニュー" Or ws.Name <> "" Then
n = n + 1
ws_name(n) = ws.Name
End If
Next
Worksheets("メニュー").Activate
For Each ws In Worksheets
For Each btn In ActiveSheet.Buttons
If ws.Name = btn.Caption Then flag = True: Exit For
Next
If flag = False And ws.Name <> "メニュー" And ws.Name <> "管理用" Then
For Each r In Range("B1:F2")
For Each btn In ActiveSheet.Buttons
If btn.TopLeftCell.Address = r.Address Then n = 100: Exit For
Next
If n <> 100 Then Set r = r: Exit For
n = 0
Next
With ActiveSheet.Buttons.Add(r.Left, r.Top, _
r.Width, r.Height)
.OnAction = "Worksheet_Hidden"
.Characters.Text = ws.Name
End With
End If
flag = False

Next
End Sub

Range("B1:F2")の範囲に追加して行きます（範囲拡張可）

この回答へのお礼

できました、すごいです。
まず非表示にする部分ですが
一つのソースでボタンの名称とシート名を突合してるから1ソースで賄えるんですね。

ちなみに私は以下の様なマクロを使用していました。
これを全員分のモジュールを作って、True、Falseだけ名前毎に設定してます。なので人数分の標準モジュールが必要です。
鈴木さんがボタンを押したときのソースです。
------------------------------------------------------------
Sub シート表示制限_鈴木()
Dim sh As Worksheet
Dim pass As String
pass = InputBox("パスワードを入力してください")
If pass <> "pass" Then
MsgBox "パスワードが違います"
Exit Sub
Else
Worksheets("鈴木").Visible = True
Worksheets("佐藤").Visible = False
Worksheets("加藤").Visible = False
Worksheets("佐々木").Visible = False
Worksheets("青木").Visible = False
Worksheets("管理用").Visible = False
End If
Worksheets("メニュー").Select
End Sub
------------------------------------------------------------
佐藤さんが鈴木さんのシートを見れないようにするためです。
個別にパスワードを設定したい場合だとやはり人数分のモジュールを用意する必要がありますよね？
ボタン設置は言う事なしでしたが上記仕様のため難しいですよね。。。

ちなみに管理者は、全員のシートが見れるうえに専用の管理者シートも存在しています。

お礼日時：2022/04/08 16:36

No.2 回答者： fujillin 回答日時： 2022/04/08 14:38

こんにちは

不明な点は適当です。

※ 「管理用」という名前のシートは存在しないものと仮定しています。
※ メニューシートのシート名は「メニュー」と仮定しています。
※ ボタンを作成する際は、CreateButtons()を実行してください。
※ ボタンの位置やサイズ等はお好みで調整してください。
※ 以下を標準モジュールにコピペのこと。


Const MenuSheet = "メニュー" ' メニューシート名
Const Manage = "管理用"

Sub CreateButtons()
Dim btn, sht
Dim i, shN()

Const btnWidth = 70
Const btnHeight = 30
Const btnLeft = 20
Const btnTop = 20
Const btnPitch = 90
Const MacroName = "MenuButtonClick"

With Worksheets(MenuSheet)
For Each btn In .Buttons
btn.Delete
Next btn

ReDim shN(1 To Worksheets.Count)
i = 1
For Each sht In Worksheets
If sht.Name <> MenuSheet Then
shN(i) = sht.Name
i = i + 1
End If
Next sht
shN(i) = Manage

For i = 1 To UBound(shN)
Set btn = .Buttons.Add(btnLeft + btnPitch * (i - 1), btnTop, btnWidth, btnHeight)
btn.Characters.Text = shN(i)
btn.OnAction = MacroName
Next i
End With
End Sub


Sub MenuButtonClick()
Dim sName, sht, flag: flag = False

If TypeName(Application.Caller) <> "String" Then Exit Sub
sName = Worksheets(MenuSheet).Buttons(Application.Caller).Characters.Text
If sName = Manage Then flag = True: sName = MenuSheet

For Each sht In Worksheets
If sht.Name <> MenuSheet Then sht.Visible = flag
Next sht
Worksheets(sName).Visible = True
Worksheets(sName).Activate
End Sub

この回答へのお礼

言葉足らず失礼しました。
管理用というシートも存在します。
管理者は管理用シートも含め全部のシートが表示されます。

No.3のお礼にも書きましたが、他人のシートが見れないようにするとなると、難しそうですね。。。

お礼日時：2022/04/08 16:37

No.1 回答者： ShowMeHow 回答日時： 2022/04/08 13:59

できますが、、、

https://www.excellovers.com/entry/2017/07/30/155 …
疑似的なコントロール配列にしないとイベントをキャッチできません。
https://gabekore.org/vba-control-array

意外とめんどくさいので、.netとかでラッパー作ったほうが早いような気もします。