Ask user for sheetname, if sheet exists then continue else restart loop

[sarao18592]

Hi, I'm struggling on how to be able to loop this function. My aim is to include this in a sub, where the user prompts fills in the sheetname.
If the sheetname exists in any of the sheets then it is selected. if it is not, then an error message pops up saying "sheet does not exist" and asks user to provide the sheetname again.

 

[edd_excelmate]

How about giving the user a combobox preloaded with sheet names from the file, then as the user types it will pre-fill with available options?

Otherwise try the following...

Option Explicit

Sub selectSheet()

Dim rWS As Worksheet        'required worksheet
    
    Set rWS = Worksheet_GetSet
    
    'do whatever you want with the sheet here...
    
End Sub


Function Worksheet_GetSet _
   (Optional setAsActiveSheet As Boolean = True, _
    Optional allowApproxMatch As Boolean = True) _
    As Worksheet

Dim ws As Worksheet
Dim ws_Name As String


giveItAnotherShot:
    Set Worksheet_GetSet = Nothing
    
    ws_Name = InputBox("Enter the sheet name", ActiveSheet.Name, ActiveSheet.Name)
    
    If ws_Name = "" Then
        If MsgBox("Selected sheet name does not exist.", vbInformation + vbRetryCancel, "Name Sheet") _
            = vbRetry Then
            GoTo giveItAnotherShot
        End If
    End If
    
'loop for reasonable match:
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(ws_Name) Then
            'sheet found!
            Set Worksheet_GetSet = ws
            GoTo foundSht
        End If
    Next ws
   
'loop for approx match
    If allowApproxMatch Then
        For Each ws In ThisWorkbook.Worksheets
            If InStr(UCase(ws.Name), UCase(ws_Name)) > 0 Then
                'approx match found!
                GoTo foundSht
            End If
        Next ws
    End If
    
Exit Function

foundSht:
    If setAsActiveSheet Then
        With ws
            .Visible = xlSheetVisible
            .Select
        End With
    End If
    
    Set Worksheet_GetSet = ws
    
End Function

 

[mohadin]

Hi
Try

Sub test()
    Dim shname
    shname = InputBox("Type sheetname", " UserPrompt")
    With ActiveWorkbook
        If Not Evaluate("ISREF('" & shname & "'!A1)") Then
            MsgBox ("Sheet NOT exist")
        Else
            Sheets(shname).Select
        End If
    End With
End Sub

 

[sarao18592]

Sub test() Dim shname shname = InputBox("Type sheetname", " UserPrompt") With ActiveWorkbook If Not Evaluate("ISREF('" & shname & "'!A1)") Then MsgBox ("Sheet NOT exist") Else Sheets(shname).Select End If End With End Sub

I tried using this but it exists the sub after it fails the first time i.e. the sheet does not exist. Is there anyway to keep the loop going or if this sub is called in another sub, is there anyway to repeat it in that sub till it succeeds?

 

[sarao18592]

How about giving the user a combobox preloaded with sheet names from the file, then as the user types it will pre-fill with available options?

The combobox option is really interesting, could prove to be a lot easier too! Do you know how I can create one on mac? Or store the result as a variable to be used further?

 

[mohadin]

Something like

Sub test()
    Dim shname
1    shname = InputBox("Type sheetname", " UserPrompt")
    With ActiveWorkbook
        If Not Evaluate("ISREF('" & shname & "'!A1)") Then
            MsgBox ("Sheet NOT exist" & vbLf & "Try again")
            GoTo 1
        Else
            Sheets(shname).Select
        End If
    End With
End Sub

 

[sarao18592]

Something like

Sub test()
    Dim shname
1    shname = InputBox("Type sheetname", " UserPrompt")
    With ActiveWorkbook
        If Not Evaluate("ISREF('" & shname & "'!A1)") Then
            MsgBox ("Sheet NOT exist" & vbLf & "Try again")
            GoTo 1
        Else
            Sheets(shname).Select
        End If
    End With
End Sub

oh wow thank you! I didn't realize just that would be enough

 

[mohadin]

Glade I could help
Thank you for the feedback
Be happy & safe

 

[edd_excelmate]

The combobox option is really interesting, could prove to be a lot easier too! Do you know how I can create one on mac? Or store the result as a variable to be used further?

add a userform and add a combo box or list box control, nothing else required on the form, user either clicks an option or clicks the 'x' to close. Dbl click on the control in the builder and it will add a click script for you where you can capture the selected form. Then pre-load the combo box in userform's initialise with a loop to get the sheets in the workbook. If you want me to knock up an example let me know.