vba error number 9 subscript out of range

Question

I'm trying to make a Excel 2007 vba code to select a sheet using userform. While testing, I'm getting 'Subscript out of range' Error if I input a sheetname which is not there in workbook. my code is below.

Private Sub Okbtn_Click()
sheetname = Me.ComboBox1.Value
If sheetname = "" Then
    Unload Me
    MsgBox "Sheet Name not enetered", vbExclamation, "Hey!"
    Exit Sub
End If

ThisWorkbook.Sheets(sheetname).Activate 'Error points here!!

On Error GoTo errmsg
'If Error.Value = 9 Then GoTo errmsg
Unload Me
MsgBox "Now you are in sheet: " & sheetname & "!", vbInformation, "Sheet Changed"

errmsg:
MsgBox ("Sheet name not found in " & ThisWorkbook.Name & " !")
End Sub

The error is in ThisWorkbook.Sheets(sheetname).Activate . I tried to put some error handing tricks before and after the problem line, but Im getting the same error-9.

Im very new to coding. I think I explained the problem correctly. I want to avoid the error from popping up, but should show a customized message instead.

Answer 1

If you move your On Error GoTo errmsg code line above the worksheet activation, the error should be handled by the error trap routine. You just need to exit the sub before reaching the same routine if successful.

    On Error GoTo errmsg
    ThisWorkbook.Sheets(sheetname).Activate 'Error points here!!

    Unload Me
    MsgBox "Now you are in sheet: " & sheetname & "!", vbInformation, "Sheet Changed"
    Exit Sub

    errmsg:
    MsgBox ("Sheet name not found in " & ThisWorkbook.Name & " !")

End Sub

Answer 2

You need to set the error handler before executing the instruction that might cause an error. Something like this

Private Sub Okbtn_Click()
sheetname = Me.ComboBox1.Value
If sheetname = "" Then
    Unload Me
    MsgBox "Sheet Name not enetered", vbExclamation, "Hey!"
    Exit Sub
End If

On Error GoTo errmsg

ThisWorkbook.Sheets(sheetname).Activate 'Error points here!!


'If Error.Value = 9 Then GoTo errmsg
Unload Me
MsgBox "Now you are in sheet: " & sheetname & "!", vbInformation, "Sheet Changed"

Exit Sub ' Avoid executing handler code when ther is no error
errmsg:
MsgBox ("Sheet name not found in " & ThisWorkbook.Name & " !")
End Sub

Answer 3

put On Error GoTo errmsg above ThisWorkbook.Sheets(sheetname).Activate

'''''
On Error GoTo errmsg
ThisWorkbook.Sheets(sheetname).Activate
'''''

error handling always must be before the line where you can receive the error

Answer 4

It would be clearer if you error handling was wrapped around a single line testing if the worksheet existed

As an example your current code will flag any error - such as the sheet being hidden - as the sheet not existing.

Private Sub Okbtn_Click()
Dim strSht As String
Dim ws As Worksheet

strSht = Me.ComboBox1.Value

If Len(strSht) = 0 Then
    Unload Me
    MsgBox "Sheet Name not entered", vbExclamation, "Hey!"
    Exit Sub
End If

On Error Resume Next
Set ws = ThisWorkbook.Sheets(strSht)
On Error GoTo 0


If Not ws Is Nothing Then
     If ws.Visible Then
        Application.Goto ws.[a1]
        MsgBox "Now you are in sheet: " & strSht & "!", vbInformation, "Sheet Changed"
    Else
        MsgBox ("Sheet exists but is hidden")
    End If
Else
    MsgBox ("Sheet name not found in " & ThisWorkbook.Name & " !")
End If

End Sub