Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
I have a sheet named "Data" that I added a combo box to. I'm trying to use
the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
hi
i am guessing that it does fire but doesn't know what to do so do nothing. change your code to this....modify to suit..... Private Sub Worksheet_Activate() Sheet1.dropdown1.Clear Sheet1.dropdown1.AddItem "Shift 1" Sheet1.dropdown1.AddItem "Shift 2" Sheet1.dropdown1.Text = dropdown1.List(0) End Sub works in xp Regards FSt1 "Scott" wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
I hate to give up on a coding task, but I just can't get it working. Perhaps
it's because I'm on Vista. Its hard for me to believe that the OS would have an effect on how Excel 2003 VBA works. Let me know if you think of any other test that may help. Is there a way with vba to iterate through a sheet and display the names of every combo box that in on a sheet? "FSt1" wrote in message ... hi i am guessing that it does fire but doesn't know what to do so do nothing. change your code to this....modify to suit..... Private Sub Worksheet_Activate() Sheet1.dropdown1.Clear Sheet1.dropdown1.AddItem "Shift 1" Sheet1.dropdown1.AddItem "Shift 2" Sheet1.dropdown1.Text = dropdown1.List(0) End Sub works in xp Regards FSt1 "Scott" wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
You used Dropdown and Input Range--did you mean that the object came from the
Forms toolbar? If yes: Option Explicit Private Sub Worksheet_Activate() Dim myDD As DropDown Set myDD = Me.DropDowns("drop down 1") With myDD .RemoveAllItems .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = 0 'nothing showing, 1 for first, 2 for second End With End Sub If you used the combobox from the Control toolbox toolbar: Option Explicit Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = -1 'nothing, 0 for the first, 1 for the second End With End Sub (Yep, the .listindex stuff is different!) Scott wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
Yes, I created the combo from using the forms toolbar. Was I right?
"Dave Peterson" wrote in message ... You used Dropdown and Input Range--did you mean that the object came from the Forms toolbar? If yes: Option Explicit Private Sub Worksheet_Activate() Dim myDD As DropDown Set myDD = Me.DropDowns("drop down 1") With myDD .RemoveAllItems .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = 0 'nothing showing, 1 for first, 2 for second End With End Sub If you used the combobox from the Control toolbox toolbar: Option Explicit Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = -1 'nothing, 0 for the first, 1 for the second End With End Sub (Yep, the .listindex stuff is different!) Scott wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
It's not a matter of right or wrong. It's a matter of what one you wanted. (I
see nothing wrong with the Dropdowns from the Forms toolbar.) But was I right with the first suggestion? <vbg Scott wrote: Yes, I created the combo from using the forms toolbar. Was I right? "Dave Peterson" wrote in message ... You used Dropdown and Input Range--did you mean that the object came from the Forms toolbar? If yes: Option Explicit Private Sub Worksheet_Activate() Dim myDD As DropDown Set myDD = Me.DropDowns("drop down 1") With myDD .RemoveAllItems .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = 0 'nothing showing, 1 for first, 2 for second End With End Sub If you used the combobox from the Control toolbox toolbar: Option Explicit Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = -1 'nothing, 0 for the first, 1 for the second End With End Sub (Yep, the .listindex stuff is different!) Scott wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Combo Box
thank you.
"Dave Peterson" wrote in message ... You used Dropdown and Input Range--did you mean that the object came from the Forms toolbar? If yes: Option Explicit Private Sub Worksheet_Activate() Dim myDD As DropDown Set myDD = Me.DropDowns("drop down 1") With myDD .RemoveAllItems .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = 0 'nothing showing, 1 for first, 2 for second End With End Sub If you used the combobox from the Control toolbox toolbar: Option Explicit Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear .AddItem "Shift 1" .AddItem "Shift 2" .ListIndex = -1 'nothing, 0 for the first, 1 for the second End With End Sub (Yep, the .listindex stuff is different!) Scott wrote: I have a sheet named "Data" that I added a combo box to. I'm trying to use the Worksheet_Activate() event to populate the combo box. After creating the combo box, I left the "Input Range" blank and entered the below code in the sheet's module. When I test the sheet by selecting it, I get an "object required" error. Just to test, I added an input range that contains blank cells, but left my code as is. After testing it, the error went away, but my code doesn't fire. I even put a test message box in to confirm that the Worksheet_Activate() event below was not firing. How can I populate a combo box that is on a sheet (not a form)? CODE: Private Sub Worksheet_Activate() dropdown1.Clear dropdown1.AddItem "Shift 1" dropdown1.AddItem "Shift 2" dropdown1.Text = dropdown1.List(0) End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to populate Combo Box ?? | Excel Programming | |||
Populate one combo box based on the selection of another combo box | Excel Programming | |||
populate combo box | Excel Discussion (Misc queries) | |||
Populate a combo box | Excel Discussion (Misc queries) | |||
Populate a combo Box | Excel Worksheet Functions |