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

質問

●質問の主旨

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)に貼り付けています。

回答No.3

end-u · ベストアンサー

>...の部分の記述を違うイベント内に書き換えました。
の結果が
>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)
：
で良い事になります。

見直してみてください。

お礼

お礼が遅くなり、大変恐縮です。

結局imogasi様のご回答やN0.3のご回答を
もとに考えたところ、下記のコードに書き換えると
補足質問で申し上げた問題点が解決できました。

'ComboBox3をクリックしたときの参照先変更
Private Sub ComboBox3_Click()

Dim te As String

te = ComboBox3.Text

With Me.ComboBox4

.ColumnCount = 2
.ColumnWidths = ";0"
.List = Worksheets(te).Range("C3", Worksheets(te).Cells(Worksheets(te).Rows.Count, 2).End(xlUp)).Value

End With

End Sub

'ComboBox4をクリックしたときの処理
Private Sub ComboBox4_Click()

With Me.ComboBox4
Me.Label8.Caption = .List(.ListIndex, 1)

End With
End Sub

このコードでも実際に使うとなると、問題が発生する
ような気がしますが、それはまた使いながら考える
ことにします。

大変ありがとうございました。

回答No.2

end-u

>２．デバックするとユーザーフォームではなく、
>標準モジュールの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プロパティで判定してください。

お礼

3日続けてご丁寧なご回答ありがとうございます。
またもや補足質問を投稿していますが、今回も
ご確認くだされば幸いです。

補足

たびたびの補足質問大変恐れ入ります。

end-u様のアドバイスをもとに

Select Case t
Case Is = ComboBox3("営業")
…

の部分の記述を違うイベント内に書き換えました。
おかげさまでデバックは発生しなくなりました。
ありがとうございます。

しかし問題点が2つでてきてどうしても解決できません。

●問題点
１．ComboBox4にWorksheets(5)において予め記載されている
仕事内容が表示されない。

２．F5キーを押したり、エクセルボタン（Alt+F11キー)押すと
とWorksheets(1)ではなく、Worksheets(4)が表示される。

追加の質問のようで大変恐縮ですが、この点についても
ご教示くだされば幸いです。よろしくお願い申し上げます。

●書き換えたコード
'ComboBox3をクリックして大分類が「営業」の場合
Private Sub ComboBox3_Change1()

Dim te As String

te = ComboBox3.Text

If te = "営業" Then

With Worksheets(5)

Set te = .Range("C3", .Cells(.Rows.Count, 2).End(xlUp))

End With

With Me.ComboBox4

.ColumnCount = 2
.ColumnWidths = ";0"
.List = ComboBox4.Value

End With

Else
Call ComboBox3_Change2

End Sub

'ComboBox3をクリックして大分類が「技術」の場合
Private Sub ComboBox3_Change2()
（以下は「営業」の場合と同じ）

回答No.1

imogasi

自分の作ったコードを長々と書いて回答者に読ませないこと。
デバックは質問者が自己でやる問題だ。データなど他者には見えない、テストできない。
ここは質問者が作ったコードの添削や、デバッグをするコーナーではない。
質問の点を絞ること。又あれやこれやの質問にせず２，３にし、別質問にわけよ。
数個なら箇条書きにでもすること。
ーー
＞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固有の勉強などが加えて必要。そういうタイプの
人を連想する。

お礼

お礼が大変遅くなり恐縮です。

imogasi様やend-u様からのご回答を
もとにNo.3でのお礼で記述したコードをもって
QNo.6845554に対する疑問を解決することが
できました。

まだまだ問題が多いと思いますが、
VBAについて少しずつ勉強していきたいと
思います。