エクセル VBA 表示範囲の簡素化

質問

よろしくお願いします。
下記構文の簡素化ができないでしょうか。
CommandButtonが30個ほどあります。
ーーーーーーーーーー
Private Sub CommandButton1_Click()
Dim rng As Range
Application.Goto Sheets("基本台紙").Range("A1")
Set rng = Range("A1:D7")
Rows.Hidden = True
rng.EntireRow.Hidden = False
Columns.Hidden = True
rng.EntireColumn.Hidden = False rng(1).Select
Unload Me
UserForm1.Show vbModeless
End Sub
ーーーーーーーーーー
Private Sub CommandButton2_Click()
Dim rng As Range
Application.Goto Sheets("基本台紙").Range("A1")
Set rng = Range("A8:B21")
Rows.Hidden = True
rng.EntireRow.Hidden = False
Columns.Hidden = True
rng.EntireColumn.Hidden = False
rng(1).Select
Unload Me
UserForm1.Show vbModeless
End Sub
ーーーーーーーーーー
Private Sub CommandButton3_Click()
Dim rng As Range
Application.Goto Sheets("基本台紙").Range("A1")
Set rng = Range("C8:D21") Rows.Hidden = True
rng.EntireRow.Hidden = False
Columns.Hidden = True
rng.EntireColumn.Hidden = False rng(1).Select
Unload Me
UserForm1.Show vbModeless
End Sub

回答No.2

f272 · ベストアンサー

クラスモジュールを使うのが嫌なら，こんな感じで...

Private Sub CommandButton1_Click()
call CommonTask("A1:D7")
End Sub
Private Sub CommandButton2_Click()
call CommonTask("A8:B21")
End Sub

Sub CommonTask(s)
Dim rng As Range
Application.Goto Sheets("基本台紙").Range("A1")
Set rng = Range(s)
Rows.Hidden = True
rng.EntireRow.Hidden = False
Columns.Hidden = True
rng.EntireColumn.Hidden = False rng(1).Select
Unload Me
UserForm1.Show vbModeless
End Sub

回答No.4

imogasi

ボタンを作るのはＦｏｒＮｅｘｔループで３０回Ａｄｄ繰り返せば仕舞。
変わるところ、ＬＥＦＴ，ＴＯＰ、などは一定であるとか、何個目かで、計算して変数で指定すれば仕舞。
ＷＩＤＴＨ、ＨＥＩＧＨＴは同じ値の場合が多いだろう。
Ｒａｎｇｅがボタンによって可変で出てくるようだが、計算して指定すればっできるだろう。
ーー
問題は
（１）エクセルで、こんな多数のボタンを作って扱うのは設計がおかしくないか？、誰かに相談すべきだ。よくわかっていてそれしかないと結論したらやむを得ないが。
こういう設計段階が、経験やスキルが如実に出るところで、扱うニーヅや取り組みの視点がおかしくはないか。思いついてすぐ方向を決めて動いているようだ。
（２）さらに、、問題は、各ボタンをクリックしたとき、どのボタンがクリックされたかのプログラムは、似た３０個のイベントプロを書かないでやる方法は、ＶＢＡでは難しいのを知っているのか？
「クラスモジュールを使う」かとか、さらにむつかしいよ。（コントロール配列がＶＢＡで使えないとかがあって）
ＶＢ．ＮＥＴ系のＶＢなどならクラスは根本だが、ＶＢＡではクラスの利用は敷居が高いと思うよ。
＝＝
シートのセルをクリックしたセルは、ＳｅｌｅｃｔｉｏｎＣｈａｎｇｅイベントのＴａｒｇｅｔでわかるから、それでＣａｓｅ文で分けるとか、クリックしたセルの行や列と関連付けて処理できないか。
＝
ボタンを散りばめたりするには、「エクセルをあまりできない人が使う」というのが理由かもしれないが、そういうケースの対応は、経験があり技量のあるプロやプロ並みの人のやること。質問ぶりから、とてもそこまで行っていると思えない。再考したらどうか。

お礼

imogasi 様
早速のご回答ありがとうございます。
シート内には写真撮影時に添付する台紙を300枚以上作成してあります。
１枚（７行２列）工事の内容によって使用する台紙の枚数及び内容が変化します。
ユーザーフォーム内のCommandButtonを押下して、それぞれの工事内容にあった範囲にあるセル（台紙）のみ表示し内容を確認する。

設計がおかしくないか？
人には人の理由があるのではないでしょうか。
経験があり技量のあるプロやプロ並みの人のやること。
プロやプロ並みの人は、質問されないと思います。
反論してすみません。

回答No.3

eden3616

ご質問のcommandbutton1～3において以下のように参照セル範囲に規則性がありません。
ご質問の情報に誤りがあるのか、参照範囲は別の方法で取得できるのか別途一覧などがありますか？

Set rng = Range("A1:D7")
Set rng = Range("A8:B21")
Set rng = Range("C8:D21")

数ある処理を集約するためには、数値や文字など変動する箇所を変数に入れて同じ処理を行うためにパターン化できることが前提にあります。
スマートなものはクラス定義ですが、冗長的ではありますが簡素に行うのであれば以下のような方法もあります。
1つのプロシージャに処理をまとめて、callで呼び出す際に対象のセル範囲を一緒に渡して処理しています。

Sub sample(myRng As String)
　　　　Dim rng As Range
　　　　Application.Goto Sheets("基本台紙").Range("A1")
　　　　Set rng = Range(myRng)
　　　　Rows.Hidden = True
　　　　rng.EntireRow.Hidden = False
　　　　Columns.Hidden = True
　　　　rng.EntireColumn.Hidden = False
　　　　rng(1).Select
　　　　Unload Me
　　　　UserForm1.Show vbModeless
End Sub

'commandbuttonクリックイベント1～30（引数に文字列でセル範囲を指定）
Private Sub CommandButton1_Click()
　　　　Call sample("A1:D7")
End Sub
Private Sub CommandButton2_Click()
　　　　Call sample("A8:B21")
End Sub
Private Sub CommandButton3_Click()
　　　　Call sample("C8:D21")
End Sub
　　　　・
　　　　・
　　　　・
Private Sub CommandButton30_Click()
　　　　Call sample("XX:XX")
End Sub

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

一部のbuttonにより処理を分岐させる必要などがあれば、引数に「ActiveControl.Name」を追加することでcontrolのオブジェクト名をsampleプロシージャに渡すことができますので、上記コードを以下のように変更することで分岐することもできます。

▼各クリックイベント内
Call sample("A1:D7")
　　　　↓
Call sample(ActiveControl.Name,"A1:D7")

▼sampleプロシージャ内
Sub sample(myRng As String)　　～　　End Sub
　　　　↓
Sub sample(btnName As String, myRng As String)
　　btnName = Replace(btnName,"CommandButton","")
　　Select Case CInt(btnName)
　　'共通の処理
　　Case 1
　　　　'commandbutton1の処理
　　Case 2, 3
　　　　'commandbutton2及び3の処理
　　Case Else
　　　　'それ以外のcommandbuttonの処理
　　End Select
　　'共通の処理
End Sub

お礼

eden3616 様
早速のご回答ありがとうございます。

回答No.1

ushi2015

こんにちは
CommandButton1からCommandButton30が有るとすると、
クラスモジュールを挿入して、（名前はClass1のまま）
Private WithEvents Btn As MSForms.CommandButton
Private Index As Integer
Public Sub NewClass(ByVal c As MSForms.CommandButton, ByVal i As Integer)
　　Set Btn = c
　　Index = i
End Sub
Private Sub Btn_Click()
　　Dim rng As Range
　　Application.Goto Sheets("基本台紙").Range("A1")
　　Set rng = Range("A1:D7").Offset(Index)
　　Rows.Hidden = True
　　rng.EntireRow.Hidden = False
　　Columns.Hidden = True
　　rng.EntireColumn.Hidden = False
　　rng(1).Select
End Sub
ユーザーフォームのコードに、
Private NumCmd(0 To 29) As New Class1
Private Sub UserForm_Initialize()
　　Dim i As Integer
　　For i = 0 To 29
　　　　NumCmd(i).NewClass Controls("CommandButton" & i + 1), i
　　Next
End Sub
のようにしてみるとどうですか？

'　　Unload Me
'　　UserForm1.Show vbModeless
は、ちょっとなんの為か良く分かりません。
UserForm1上のコマンドボタンの話ですよね？

お礼

ushi2015 様
早速のご回答ありがとうございます。
'　　Unload Me
'　　UserForm1.Show vbModeless
は、UserForm2を閉じてシートを表示します。
そのシート上にUserForm1を表示して、クリックすると
再度UserForm2を表示させるためです。