コンボボックスの値で参照するワークシートを変えたい
●質問の主旨
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)に貼り付けています。
お礼
ありがとうございました。 すっきりしました。 Initializeに書くにせよ、結局EnterにおいてMe!ComboBox○.DropDownとかComboBox○.ListIndex = 1とか書かざるを得ないようなので、この度はEnterに一元化することにしました。 ありがとうございました。