I have a potentially unique situation... I have a userform with a multipage that contains 20 comboboxes which all reference the same list of items. The rowsource for each of these comboboxes is a list of search options which each have 1 of 3 different logical dependencies (the search text contains, vs the search value is between value1 & value2, vs the search is logical; a true/false option.)
To the side of each of these comboboxes are a set of textboxes, checkboxes, and labels stacked on top of eachother which are preset to .visible = false. When the value in the combox is changed, its Before_Update event triggers to set the appropriate textbox(s), checkbox(s), and/or label(s) to .visible = true based on the appropriate logical dependency for the selected search option. All but the first of these 20 comboboxes have .visible preset to false as well, and then when the first textbox or checbox changes (again via the before_update even for each), the next combobox is set to .visible = true so that another search option can be picked from the list.
It is set up this way because the list of search options is overwhelming if they are all visible at the same time. While it is highly unlikely that a user will ever need more than 10 or so of the available options at once, the number of available options exceed 180, so having them all on the form visible is simply not an option...
The problem I'm having is that when .visible = true is set for the appropriate textboxes, checkboxes, and labels, I can't for the life of me get the focus to set on the appropriate control. The after_Update even doesn't work either. The change event would work, except that I want the users to be able to scroll using up and down arrows through the list of search options in each combobox, so the change event would trigger with each new selection and exit the combobox prematurely... Obviously, I can't have that, so...
My idea is that in the multipage change event, I could specify that when on this page (page 6 in this case), the event could identify the active control's tabindex and specifically select the next control based on that tabindex. (something like activecontrol.tabindex + 1), but I don't know how or if there is a way to do this.
Any help is much appreciated!
For your review, the following is part of the code I have for a single combobox and its associated labels, textboxes, and checkboxes. Aside from setting focus appropriately, the code functions as I had hoped.
The control names might be a tad confusing, so here's an explanation:
"SearchOption1" is the first combobox.
"SO1Contains" is a textbox for searching for text containing said value
"SO1Between1" and "SO1Between2" are textboxes for values to search between
"SO1Between3" is a label that goes between them ("and")
"SO1CB" is a true/false checkbox for searching where a value is true or false
"LblS2" is the label for 2nd combobox, titled "SearchOption2", both of which are made visible by changes to the above textboxes, and checbox.
Private Sub SearchOption1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Me.MP1.Value = 6 Then
'do what needs done
Dim OptNum As Integer
Dim DataAddrs As String
Dim LgcArg As String
Dim ArgType As String
If Me.SearchOption1.Value = vbNullString Then
Cancel = True
Exit Sub
Else
OptNum = Left(Me.SearchOption1.Value, WorksheetFunction.Find(":", Me.SearchOption1.Value) - 1) * 1
DataAddrs = Sheets("Defs").Range("Q" & OptNum + 100).Value
LgcArg = Sheets("Defs").Range("R" & OptNum + 100).Value
ArgType = Sheets("Defs").Range("T" & OptNum + 100).Value
If Me.SearchOption1.Value <> vbNullString Then
Sheets("Defs").Range("B61").Value = "Yes"
Sheets("Defs").Range("C61").Value = OptNum + 100
Sheets("Defs").Range("D61").Value = LgcArg
Sheets("Defs").Range("E61").Value = ArgType
Sheets("Defs").Range("J61").Value = DataAddress
Else
Sheets("Defs").Range("B61").Value = "No"
Sheets("Defs").Range("C61").Value = vbNullString
Sheets("Defs").Range("D61").Value = vbNullString
Sheets("Defs").Range("E61").Value = vbNullString
Sheets("Defs").Range("J61").Value = vbNullString
End If
If LgcArg = "Contains" Then
Me.SO1Contains.Visible = True
Else
Me.SO1Contains.Visible = False
End If
If LgcArg = "Between" Then
Me.SO1Between1.Visible = True
Me.SO1Between2.Visible = True
Me.SO1Between3.Visible = True
Else
Me.SO1Between1.Visible = False
Me.SO1Between2.Visible = False
Me.SO1Between3.Visible = False
End If
If LgcArg = "True/False" Then
Me.SO1CB.Visible = True
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
Else
Me.SO1CB.Visible = False
End If
End If
End If
End Sub
Private Sub SO1Between1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Sheets("Defs").Range("E61").Value = "Date" Then
If Not IsDate(Me.SO1Between1.Value) Then
MsgBox "You must enter an actual date."
Me.SO1Between1.Value = vbNullString
Cancel = True
Exit Sub
End If
Else
If Sheets("Defs").Range("E61").Value = "Value" Then
If Not IsNumeric(Me.SO1Between1.Value) Then
MsgBox "You must enter an actual number."
Me.SO1Between1.Value = vbNullString
Cancel = True
Exit Sub
End If
End If
End If
End Sub
Private Sub SO1Between2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Sheets("Defs").Range("E61").Value = "Date" Then
If Not IsDate(Me.SO1Between2.Value) Then
MsgBox "You must enter an actual date."
Me.SO1Between2.Value = vbNullString
Cancel = True
Exit Sub
Else
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
Me.SearchOption2.SetFocus
End If
Else
If Sheets("Defs").Range("E61").Value = "Value" Then
If Not IsNumeric(Me.SO1Between2.Value) Then
MsgBox "You must enter an actual number."
Me.SO1Between2.Value = vbNullString
Cancel = True
Exit Sub
Else
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
Me.SearchOption2.SetFocus
End If
End If
End If
End Sub
Private Sub SO1Contains_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Me.SO1Contains.Value = vbNullString Then
Exit Sub
Else
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
Me.SearchOption2.SetFocus
End If
End Sub
Bookmarks