combobox text not on list error handling VBA

Question

I have a workbook with about 130 sheets in it, which I select from a combobox on the main page. I populate the combobox using rowsource, from a named range which has the sheet names in it. It works great if you select one of the sheet names from the list, and it closes nicely if you don't enter anything and still press submit. I want to cover the case where the user types in something not on the list. I want to say something to VBA like 'if sWs is not equal to something from the named range, then show a message box which says 'error'', or whatever. Here is my code below, with my notes:

'if submit button is pressed go to selected worksheet or close the userform:
   Private Sub Submit_Click()
   Dim sWs As String
   'the string is the text from combobox, called 'Title', selected on front page:
   sWs = Me.Title.Value
      'if nothing is selected then close the userform:
      If sWs = ("") Then
      End
      End If
      'If an item is selected from the dropdown list then show that worksheet and close the userform:
      Worksheets(sWs).Select
      End
      End Sub

'If exit button is pressed close the userform:
   Private Sub ExitButton_Click()
   End 
   End Sub

I think I need to be using a Boolean, so I can specify what to do when it is false, but not sure how to write it. Sorry, I'm a bit of a noob.

Any ideas?

xn1 xn1 · Accepted Answer · 2016-08-31T09:04:02

Try checking the list index of the item selected, -1 would indicate something has been entered that is not valid

'if submit button is pressed go to selected worksheet or close the userform:

   Private Sub Submit_Click()
   Dim sWs As String

   'the string is the text from combobox, called 'Title', selected on front page: 
   If Me.Title.ListIndex = - 1 Then
     MsgBox "Invalid Entry"
     End
   End If

      sWs = Me.Title.Value

      'if nothing is selected then close the userform:
      If sWs = ("") Then
      End
      End If

      'If an item is selected from the dropdown list then show that worksheet and close the userform:
      Worksheets(sWs).Select
      End
      End Sub

'If exit button is pressed close the userform:
   Private Sub ExitButton_Click()
   End 
   End Sub

combobox text not on list error handling VBA

1 Answers