VB Combobox field problem in Excel 2000

[H2]

Hi all,

I'm wondering if somebody could please help me with a problem I'm
having when creating a Combobox field in Excel 2000.

I created the following VB code in an Excel Workbook. Is related to two
Combobox fields that have been placed in one of the sheets within the
workbook. However, when I close the excel workbook and the open it
again, both Combobox fields fail to initialize. I have to open VB and
click on the 'Run Sub/User Form' 'play' icon in order for the Combobox
fields to work correctly. I'm an not an experieced VB coder, so I will
really appreciate it if somebdoy could please give me an idea of how to
get these combobox fields to initialize automatically one I open the
workbook. Thanks

Private Sub Worksheet_Initialize()

'Added list entries to combo box 1 MENU. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.

ComboBox1.AddItem "" 'ListIndex = 0
ComboBox1.AddItem "hardware CPU" 'ListIndex = 1
ComboBox1.AddItem "software" 'ListIndex = 2
ComboBox1.AddItem "printers" 'ListIndex = 3
ComboBox1.AddItem "scanners" 'ListIndex = 4
ComboBox1.AddItem "photocopiers" 'ListIndex = 5
ComboBox1.AddItem "books" 'ListIndex = 6
ComboBox1.AddItem "manuals" 'ListIndex = 7
ComboBox1.AddItem "telephones" 'ListIndex = 8
ComboBox1.AddItem "KVM Switches" 'ListIndex = 9
ComboBox1.AddItem "Safes" 'ListIndex = 10

'Use drop-down list
ComboBox1.Style = fmStyleDropDownList
'Combo box values are ListIndex values
ComboBox1.BoundColumn = 0
'Set combo box to first entry
ComboBox1.ListIndex = 0

End Sub

Private Sub ComboBox1_Click()
Select Case ComboBox1.Value
Case 1 'hardware CPU
Sheets("Hardware").Select

Case 2 'software
Sheets("Software").Select

Case 3 'printers
Sheets("Printers").Select

Case 4 'scanners
Sheets("Scanners").Select

Case 5 'photocopiers
Sheets("Photocopiers").Select

Case 6 'books
Sheets("Books").Select

Case 7 'manuals
Sheets("Manuals").Select

Case 8 'telephones
Sheets("Telephones").Select

Case 9 'KVM Switches
Sheets("KVM").Select

Case 10 'Safes
Sheets("Safes").Select

End Select

End Sub

Private Sub Worksheet_Initialize2()

'Add list entries to combo box 1 MENU. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.
ComboBox2.AddItem "" 'ListIndex = 0
ComboBox2.AddItem "IID22 IRRS/A2G" 'ListIndex = 1
ComboBox2.AddItem "Passports" 'ListIndex = 2

'Use drop-down list
ComboBox2.Style = fmStyleDropDownList
'Combo box values are ListIndex values
ComboBox2.BoundColumn = 0
'Set combo box to first entry
ComboBox2.ListIndex = 0

End Sub

Private Sub ComboBox2_Click()
Select Case ComboBox2.Value
Case 1 'IID22 IRRS A2G
Sheets("IID22 IRRS A2G").Select

Case 2 'BP58 Passport Data Access
Sheets("Passports").Select

End Select

End Sub

 

[Ikaabod]

In excel type Alt-F11 at the top left double-click "ThisWorkbook".
Paste the following code:
Private Sub Workbook_Open()
Call Worksheet_Initialize
Call Worksheet_Initialize2
End Sub

Hope that answers your question.

 

[H2]

Thanks for your reply Ikaabod. But unfortunately I am getting a Compile
error message - 'Sub or Function not defined' after I paste that piece
of code in the "ThisWorkbook" file. I would appreciated if you know
what could be causing the problem. Thanks

 

[Ikaabod]

How about this("Sheet1" is whatever sheet you have your combo boxes
in):
Private Sub Workbook_Open()

'Added list entries to combo box 1 MENU. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.

Sheets("Sheet1").ComboBox1.AddItem "" 'ListIndex = 0
Sheets("Sheet1").ComboBox1.AddItem "hardware CPU" 'ListIndex = 1
Sheets("Sheet1").ComboBox1.AddItem "software" 'ListIndex = 2
Sheets("Sheet1").ComboBox1.AddItem "printers" 'ListIndex = 3
Sheets("Sheet1").ComboBox1.AddItem "scanners" 'ListIndex = 4
Sheets("Sheet1").ComboBox1.AddItem "photocopiers" 'ListIndex = 5
Sheets("Sheet1").ComboBox1.AddItem "books" 'ListIndex = 6
Sheets("Sheet1").ComboBox1.AddItem "manuals" 'ListIndex = 7
Sheets("Sheet1").ComboBox1.AddItem "telephones" 'ListIndex = 8
Sheets("Sheet1").ComboBox1.AddItem "KVM Switches" 'ListIndex = 9
Sheets("Sheet1").ComboBox1.AddItem "Safes" 'ListIndex = 10

'Use drop-down list
Sheets("Sheet1").ComboBox1.Style = fmStyleDropDownList
'Combo box values are ListIndex values
Sheets("Sheet1").ComboBox1.BoundColumn = 0
'Set combo box to first entry
Sheets("Sheet1").ComboBox1.ListIndex = 0


'Add list entries to combo box 1 MENU. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.
Sheets("Sheet1").ComboBox2.AddItem "" 'ListIndex = 0
Sheets("Sheet1").ComboBox2.AddItem "IID22 IRRS/A2G" 'ListIndex = 1
Sheets("Sheet1").ComboBox2.AddItem "Passports" 'ListIndex = 2

'Use drop-down list
Sheets("Sheet1").ComboBox2.Style = fmStyleDropDownList
'Combo box values are ListIndex values
Sheets("Sheet1").ComboBox2.BoundColumn = 0
'Set combo box to first entry
Sheets("Sheet1").ComboBox2.ListIndex = 0

End Sub

Sorry about my original post being wrong. Hopefully this works for
you.