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

質問

• エクセルのVBAを使用して表示範囲を簡素化する方法について教えてください。
• CommandButtonが複数ある場合、それぞれのボタンをクリックした際に表示範囲を適切に設定する方法を教えてください。
• 基本台紙シートで特定の範囲を表示させるためのVBAコードを簡素化したいです。

回答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 様
早速のご回答ありがとうございます。