コンボボックスの値で参照するワークシートを変えたい

Question

●質問の主旨

ComboBox3で選択した文字列によって、参照するワークシートを
変え、そのワークシートからComboBox4に代入するには、以下の
コードをどのように書き換えればよいでしょうか？
ご教示願います。

●質問の補足

添付画像でComboBox3に「営業」、「技術」、「総務」まで
入力することによってそれぞれ３つのワークシートを参照
させたいと考えています。

・「営業」→中分類（営業）シート
・「技術」→中分類（技術）シート
・「総務」→中分類（総務）シート

そして各シートにはそれぞれ異なった仕事内容の表が
既に作成されています。もしComboBox3で「営業」を
選択したなら、ComboBox4で中分類（営業）シートに
記載されている仕事内容を選択できるようにしたいと
考えています。

そのためSelect Caseステートメントを使って
ComboBox3の内容によってComboBox4の内容を変える
コードを作成したつもりです。

●現在の問題点

１．下記のコードを実行しようとすると、

「実行時エラー91
オブジェクト変数またはWithブロック変数が設定されていません」

というエラーが返されます。

２．デバックするとユーザーフォームではなく、
標準モジュールの2行目

UserForm1.Show

が黄色くなります。

●コード
（標準モジュール）
Sub 日報記入ダイアログ()
    UserForm1.Show
End Sub

（ユーザーフォーム）
Option Explicit

Private Sub ComboBox4_Change()

End Sub

'ユーザーフォームの初期化
Private Sub UserForm_Initialize()
    
 Dim r As Range
 Dim n As Range
 Dim d As Range
 Dim t As Range
 
 With Worksheets(2)
    
       Set r = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
 
   End With

With Me.ComboBox1
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = r.Value
    End With
 
 With Worksheets(3)
     
     Set n = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
  
  End With
  
  With Me.ComboBox2
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = n.Value
    End With
    
  With Worksheets(4)
    
    Set d = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
  
  End With
    
 With Me.ComboBox3
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = d.Value
    End With

'中分類のComboBox4は「大分類」の選択内容によって参照するワークシートが変わる
 
 Select Case t
 
 Case Is = ComboBox3("営業")
  
 With Worksheets(5)
    
       Set t = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
 
   End With

With Me.ComboBox4
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = t.Value
    End With
    
    Case Is = ComboBox3("技術")
  
 With Worksheets(6)
    
       Set t = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
 
   End With

With Me.ComboBox4
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = t.Value
    End With
    
     Case Is = ComboBox3("総務")
  
 With Worksheets(7)
    
       Set t = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
 
   End With

With Me.ComboBox4
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .List = t.Value
        
    End With
        
 End Select

'メモリの解放
    Set r = Nothing
    Set n = Nothing
    Set d = Nothing
    Set t = Nothing

'その他の初期値
    TextBox3.Value = Worksheets(1).Range("a10000").End(xlUp).Row
    txtdate = Date
    
 End Sub

'ComboBox1をクリックしたときの処理
Private Sub ComboBox1_Click()
 
   With Me.ComboBox1
        Me.Label19.Caption = .List(.ListIndex, 1)
          
    End With

End Sub

'ComboBox2をクリックしたときの処理
Private Sub ComboBox2_Click()
 
   With Me.ComboBox2
        Me.Label20.Caption = .List(.ListIndex, 1)
    
    End With

End Sub

'ComboBox2をクリックしたときの処理(中分類の仕事によってスターマークが変わる）

'フォームからデータベースへの転記
Private Sub CommandButton3_Click()
  
  Dim Rowpos As Long
  Dim ColPos As Long
   
   Rowpos = Worksheets(1).Range("a10000").End(xlUp).Row
   ColPos = 1

Rowpos = Rowpos + 1
 
 With Worksheets(1)
  
  .Cells(Rowpos, ColPos) = TextBox3.Value
  .Cells(Rowpos, ColPos + 1) = txtdate.Value
  .Cells(Rowpos, ColPos + 2) = Label19.Caption
  .Cells(Rowpos, ColPos + 3) = ComboBox1.Text
  .Cells(Rowpos, ColPos + 4) = ComboBox2.Text
  .Cells(Rowpos, ColPos + 5) = Label20.Caption
  .Cells(Rowpos, ColPos + 6) = ComboBox3.Text
 
 End With
 
 'Noの加算
  TextBox3.Value = TextBox3.Value + 1

Call Clearcmb

End Sub

'データベース入力後にコンボボックスを空欄にする
Private Sub Clearcmb()
    
    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""

End Sub

'ユーザーフォームの終了
Private Sub CommandButton5_Click()
   
   Unload UserForm1
   End
   
End Sub

以上よろしくお願い申し上げます。使用機種はWindowsVistaで、
Excel2007です。私はVBA初心者です。添付の画像でのユーザーフォームは
プリントスクリーンでWorksheet(1)に貼り付けています。

end-u · Accepted Answer

>...の部分の記述を違うイベント内に書き換えました。
の結果が
>Private Sub ComboBox3_Change1()
ですか？
そのプロシージャ名ではイベント実行されません。

Formモジュールに限らず、SheetモジュールやThisWorkbookモジュールなどに記述するイベントプロシージャは、
VBEコードウィンドウの上部に２つ並んでいるドロップダウンリスト、
[(General)　▼][(Declarations)　▼]
の各パートから対象コントロールとイベント種別を選択してコード記述します。
http://excelvba.pc-users.net/fol9/9_3.html

それとも
Private Sub ComboBox3_Change()
のイベントコードから Call ComboBox3_Change1 しているのでしょうか？
それはムダな処理です。

また、
Dim te As String
teは文字列型変数ですね。Object型変数ではありませんから
Set te = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))
これではコンパイルエラーです。

また、Select Caseで分岐させる事を考えるのではなく、
>'ComboBox3をクリックして大分類が「営業」の場合
目的のワークシートの名前が　"営業"　だったら、
A No.1のimogasiさんのアドバイスのように
With Worksheets("営業")
：
とワークシート名で指定できます。
変数teに目的のワークシート名をセットする事ができるなら
With Worksheets(te)
：
で良い事になります。

見直してみてください。

end-u · Answer

>２．デバックするとユーザーフォームではなく、
>標準モジュールの2行目
>UserForm1.Show
>が黄色くなります。
そこから[F8]キーを押すたびに１ステップずつ処理がすすみます。
エラー箇所を特定してください。

http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html
デバッグは基本です。マスターしてください。

>Select Case t
ここで止まるハズです。
t には何もSetされてませんから。

そもそもUserForm_Initializeイベントは
>'ユーザーフォームの初期化
と書かれているように、UserFormを読み込む時に走るイベントです。
>ComboBox3で選択した文字列によって、参照するワークシートを変え...
ComboBox3を選択するのはユーザー操作ですよね。
ユーザーフォームの初期化イベントではないです。
違うイベント内に書いてください。
その際、
ComboBox3("営業")
という書き方はしません。
ValueプロパティやTextプロパティで判定してください。

imogasi · Answer

自分の作ったコードを長々と書いて回答者に読ませないこと。
デバックは質問者が自己でやる問題だ。データなど他者には見えない、テストできない。
ここは質問者が作ったコードの添削や、デバッグをするコーナーではない。
質問の点を絞ること。又あれやこれやの質問にせず２，３にし、別質問にわけよ。
数個なら箇条書きにでもすること。
ーー
＞ComboBox3で選択した文字列によって、参照するワークシートを変えなら
そもそも、ワークシートを特定する（掴む）のは
A.シートのインデクす番号による
B.ワークシートの名前による
C。その他(略）
しかないのだ。A.は使途のタブの位置が変わったりすると変わッ田利するので、普通は名前によって特定する。
この点の勉強がしっかりされていない。
あとはソノシートは
s=ComboBox3.text
Worksheets(s).Range("a2").Select
などを書くだけ。
Sub test01()
s = "Sheet3"  '"ComboBox3.Text
Worksheets(s).Select
End Sub
シートによって処理を分けるなら、上記のあとの行にしては。
全般的にエクセルVBA（とコントロール）の勉強不足で、自分が学んだ、他の場合で使うコードを強引に他にも使おうとしているようだ。
VBや他言語など経験があっても、エクセル固有の勉強やエクセルVBA固有の勉強などが加えて必要。そういうタイプの
人を連想する。

コンボボックスの値で参照するワークシートを変えたい

このQ&Aに関連する最新のQ&A

>...の部分の記述を違うイベント内に書き換えました。

>２．デバックするとユーザーフォームではなく、

この回答への補足

自分の作ったコードを長々と書いて回答者に読ませないこと。

このQ&Aに関連する人気のQ&A

関連するQ&A

関連するカテゴリからQ&Aを探す

人気Q&Aランキング