Well as far as I can tell, the fix was just to place .setfoucs in the before_update event and .visible in the change event for each of the 20 comboboxes. I would love a more elegant solution if anyone has any. In the meantime, here's what I've come up with; duplicated per comboxbox, textbox, checkbox, & label set. It's a lot of code (80,000 more words than this thing will let me post...), but it works...
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.SetFocus
End If
If LgcArg = "Between" Then
Me.SO1Between1.SetFocus
End If
If LgcArg = "True/False" Then
Me.SO1CB.SetFocus
End If
End If
End If
End Sub
Private Sub SearchOption1_Change()
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
LgcArg = Sheets("Defs").Range("R" & OptNum + 100).Value
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.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.SetFocus
End If
End If
End If
End Sub
Private Sub SO1Between2_Change()
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
End Sub
Private Sub SO1Contains_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Me.SO1Contains.Value = vbNullString Then
MsgBox "You must enter something to search for..."
Cancel = True
Exit Sub
Else
Me.SearchOption2.SetFocus
End If
End Sub
Private Sub SO1Contains_Change()
Me.SearchOption2.Visible = True
Me.LblS2.Visible = True
End Sub
Bookmarks