Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
initializing multiple comboboxes
i am trying to initialize all the comboboxes in a userform by iterating
through them and setting their .list and their .listindex. values. here is my code: Private Sub UserForm_Initialize() Dim ctl As Control Dim WT As Range Dim DT As Range WT = Sheets("sheet2").Range("W_T") DT = Sheets("sheet2").Range("D_T") Me.Caption = ActiveCell.Value For Each ctl In Me.Controls If TypeName(ctl) = "ComboBox" Then ctl.List = WT.Value ctl.ListIndex = 0 End If Next ctl ComboBox7.List = DT.Value ComboBox7.ListIndex = 0 TextBox1.Value = 0 End Sub I am getting an error that says "object variable or with block variable not set" on the line of code (elsewhere) that displays the userform "VBA.UserForms.Add(UnitType).Show" This line has worked in the up till now, so i am pretty sure that the problem is in the code above, and i am pretty sure it is in the two lines that begin "ctl.list" and "ctl.listindex". I was hoping and guessing that this syntax would work, but it looks like i am wrong. any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
initializing multiple comboboxes
Try this
Dim ctl As Control Dim WT Dim DT WT = Sheets("sheet2").Range("W_T") DT = Sheets("sheet2").Range("D_T") Me.Caption = ActiveCell.Value For Each ctl In Me.Controls If TypeName(ctl) = "ComboBox" Then ctl.List = WT ctl.ListIndex = 0 End If Next ctl ComboBox7.List = DT ComboBox7.ListIndex = 0 TextBox1.Value = 0 -- HTH RP (remove nothere from the email address if mailing direct) "natanz" wrote in message ups.com... i am trying to initialize all the comboboxes in a userform by iterating through them and setting their .list and their .listindex. values. here is my code: Private Sub UserForm_Initialize() Dim ctl As Control Dim WT As Range Dim DT As Range WT = Sheets("sheet2").Range("W_T") DT = Sheets("sheet2").Range("D_T") Me.Caption = ActiveCell.Value For Each ctl In Me.Controls If TypeName(ctl) = "ComboBox" Then ctl.List = WT.Value ctl.ListIndex = 0 End If Next ctl ComboBox7.List = DT.Value ComboBox7.ListIndex = 0 TextBox1.Value = 0 End Sub I am getting an error that says "object variable or with block variable not set" on the line of code (elsewhere) that displays the userform "VBA.UserForms.Add(UnitType).Show" This line has worked in the up till now, so i am pretty sure that the problem is in the code above, and i am pretty sure it is in the two lines that begin "ctl.list" and "ctl.listindex". I was hoping and guessing that this syntax would work, but it looks like i am wrong. any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
initializing multiple comboboxes
thanks but that is not working. It seems like i need to use a "set"
statement somewhere. but i am not sure where. I am self taught at this and there are apparently some major gaps. advice? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
initializing multiple comboboxes
Hi
the ranges are not set Try Set WT = Sheets("sheet2").Range("W_T") Set DT = Sheets("sheet2").Range("D_T") carlos "natanz" wrote in message ups.com... i am trying to initialize all the comboboxes in a userform by iterating through them and setting their .list and their .listindex. values. here is my code: Private Sub UserForm_Initialize() Dim ctl As Control Dim WT As Range Dim DT As Range WT = Sheets("sheet2").Range("W_T") DT = Sheets("sheet2").Range("D_T") Me.Caption = ActiveCell.Value For Each ctl In Me.Controls If TypeName(ctl) = "ComboBox" Then ctl.List = WT.Value ctl.ListIndex = 0 End If Next ctl ComboBox7.List = DT.Value ComboBox7.ListIndex = 0 TextBox1.Value = 0 End Sub I am getting an error that says "object variable or with block variable not set" on the line of code (elsewhere) that displays the userform "VBA.UserForms.Add(UnitType).Show" This line has worked in the up till now, so i am pretty sure that the problem is in the code above, and i am pretty sure it is in the two lines that begin "ctl.list" and "ctl.listindex". I was hoping and guessing that this syntax would work, but it looks like i am wrong. any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
initializing multiple comboboxes
No, because you need to load an array, not a range variable.
I tested it, it works. -- HTH RP (remove nothere from the email address if mailing direct) "natanz" wrote in message oups.com... thanks but that is not working. It seems like i need to use a "set" statement somewhere. but i am not sure where. I am self taught at this and there are apparently some major gaps. advice? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple ComboBoxes | Excel Programming | |||
Initializing a Form with multiple forms embedded | Excel Programming | |||
Populate multiple comboboxes with same lists | Excel Programming | |||
Populate multiple comboboxes with same lists | Excel Programming | |||
Multiple ComboBoxes and TextBoxes on Userform | Excel Programming |