|
VB Combobox field problem in Excel 2000
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 |
VB Combobox field problem in Excel 2000
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 Wrote: 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 ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=542768 |
VB Combobox field problem in Excel 2000
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 :) |
VB Combobox field problem in Excel 2000
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. H2 Wrote: Compile error message - 'Sub or Function not defined' after I paste that piece of code in the "ThisWorkbook" file. -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=542768 |
| All times are GMT +1. The time now is 05:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com