Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim CTL as MSForms.Control always sets as Control/Combobox
In the following code, I would like to be able to dim a listbox variable,
lboBox, and then insert the following after the first loop line: For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ListBox Then set lboBox = CTL Elseif Typeof CTL is MSForms.Combobox then set cboBox = CTL end if I can't figure out seems always (from the very beginning) Dimmed as a combobox control, and therefore skips the first IF Then line EVEN when the control is a ListBox. Any thoughts would be greatly appreciated. My problem code: Private Sub InitializeIndicators(frmSettings As MSForms.UserForm) On Error GoTo InitializeIndicators_Err Dim cboBox As MSForms.ComboBox Dim strboxName As String Dim i As Integer Dim CTL As MSForms.Control For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ComboBox Then If InStr(1, LCase(CTL.Name), LCase("lboIndicator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboIndicator"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboPeriod")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboPeriod"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorLeft"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorRight"), 1) End If End If Next Set cboBox = Nothing Exit Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim CTL as MSForms.Control always sets as Control/Combobox
Not 100% sure what you are after, as you mix terms: The function FillListBox
has arguments of cboBox and lboIndicator. But something like this ? Dim WorkingBox As MSForms.Control Dim strboxName As String Dim i As Integer Dim CTL As MSForms.Control For Each CTL In frmSettings.Controls If (TypeOf CTL Is MSForms.ComboBox) Or (TypeOf CTL Is MSForms.ListBox) Then Set WorkingBox=CTL Call FillListBox(WorkingBox, ListRows("lboIndicator"), 0) NickHK "Post Tenebras Lux" wrote in message ... In the following code, I would like to be able to dim a listbox variable, lboBox, and then insert the following after the first loop line: For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ListBox Then set lboBox = CTL Elseif Typeof CTL is MSForms.Combobox then set cboBox = CTL end if I can't figure out seems always (from the very beginning) Dimmed as a combobox control, and therefore skips the first IF Then line EVEN when the control is a ListBox. Any thoughts would be greatly appreciated. My problem code: Private Sub InitializeIndicators(frmSettings As MSForms.UserForm) On Error GoTo InitializeIndicators_Err Dim cboBox As MSForms.ComboBox Dim strboxName As String Dim i As Integer Dim CTL As MSForms.Control For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ComboBox Then If InStr(1, LCase(CTL.Name), LCase("lboIndicator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboIndicator"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboPeriod")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboPeriod"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorLeft"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorRight"), 1) End If End If Next Set cboBox = Nothing Exit Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim CTL as MSForms.Control always sets as Control/Combobox
Thanks. I think I worked it out. I also didn't realize that
ListRows("lboIndicator") is a function that returns a list (from an array) that fills the combo or list box. The "lboIndicator" is used in an Instr function to choose which set of data to put in the array. There are other "OtherStrings" that return the same array, but filled with different data. I don't really know what was wrong, but when I rebuilt the function elsewhere in the code it worked fine. Same code. Go figure. Appreciate the suggestion. "NickHK" wrote: Not 100% sure what you are after, as you mix terms: The function FillListBox has arguments of cboBox and lboIndicator. But something like this ? Dim WorkingBox As MSForms.Control Dim strboxName As String Dim i As Integer Dim CTL As MSForms.Control For Each CTL In frmSettings.Controls If (TypeOf CTL Is MSForms.ComboBox) Or (TypeOf CTL Is MSForms.ListBox) Then Set WorkingBox=CTL Call FillListBox(WorkingBox, ListRows("lboIndicator"), 0) NickHK "Post Tenebras Lux" wrote in message ... In the following code, I would like to be able to dim a listbox variable, lboBox, and then insert the following after the first loop line: For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ListBox Then set lboBox = CTL Elseif Typeof CTL is MSForms.Combobox then set cboBox = CTL end if I can't figure out seems always (from the very beginning) Dimmed as a combobox control, and therefore skips the first IF Then line EVEN when the control is a ListBox. Any thoughts would be greatly appreciated. My problem code: Private Sub InitializeIndicators(frmSettings As MSForms.UserForm) On Error GoTo InitializeIndicators_Err Dim cboBox As MSForms.ComboBox Dim strboxName As String Dim i As Integer Dim CTL As MSForms.Control For Each CTL In frmSettings.Controls If TypeOf CTL Is MSForms.ComboBox Then If InStr(1, LCase(CTL.Name), LCase("lboIndicator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboIndicator"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboPeriod")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboPeriod"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorLeft"), 0) ElseIf InStr(1, LCase(CTL.Name), LCase("lboOperator")) Then Set cboBox = CTL Call FillListBox(cboBox, ListRows("lboOperatorRight"), 1) End If End If Next Set cboBox = Nothing Exit Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
form control combobox | Excel Discussion (Misc queries) | |||
Control ToolBox ComboBox | Excel Discussion (Misc queries) | |||
Using Multiple sets of Option Buttons (Form Control) In Excell | Excel Worksheet Functions | |||
Excel Combobox Box Control | Excel Programming | |||
MsForms.Control Event Class | Excel Programming |