1. I am writing a VBA code where I need to find if sheet name given by user through inputbox is available or not in a workbook containing many sheets.

2. But if the sheet name is not available then the inputbox pops up again to enter the sheet name so it can search again.

I have written 1st part of the code which is working fine but I need help with the 2nd part(if the sheet name is not available). Let me know if this is possible in for loop or I have to use other loop?

 Sub callbyinputbox()
    Dim pendworkbook As Workbook
    Dim sht As Worksheet
    Dim entername As String
    
    Set pendworkbook = Workbooks("pend_app_new.xlsx")
    
    entername = InputBox("Enter name", "Search Sheet")
    
    For Each sht In pendworkbook.Worksheets
    
        If sht.Name = entername Then
             pendworkbook.Sheets(entername).Activate
             Exit Sub
        End If
    
    Next sht
    
    MsgBox ("You entered " & entername & vbNewLine & "Sheet by this name is not available")

end sub

CodePudding user response：

Here is your code:

entername = InputBox("Enter name", "Search Sheet")

For Each sht In pendworkbook.Worksheets

    If sht.Name = enterDate Then
         pendworkbook.Sheets(entername).Activate
         Exit Sub
    End If

Next sht

You store the response from the user in a variable: entername.

You then loop through all the sheets and check if the name matches a variable called enterDate.

Change this to entername and it will then have something to match against, and the If block will run.

Check out using Option Explicit - this would have highlighted this issue for you.

UPDATE:

This is probably breaking an unwritten rule somewhere, but a simple Do Until False loop, which will permanently run (until the Exit Sub condition is reached and breaks the loop) will keep asking until a valid sheetname is input.

Alternatively, you could use a For.. Next loop. That way, you could set a maximum number of prompts before giving up..

Note: I have made this comparison case insensitive - to give the user a better chance of inputting a correct sheet name.

Sub callbyinputbox()

    Dim pendworkbook As Workbook
    Dim sht As Worksheet
    Dim entername As String
    
    Set pendworkbook = Workbooks("pend_app_new.xlsx")
    
    Do Until False
    
        entername = InputBox("Enter name", "Search Sheet")
        
        For Each sht In pendworkbook.Worksheets
        
            If LCase(sht.Name) = LCase(entername) Then
                 pendworkbook.Sheets(entername).Activate
                 Exit Sub
            End If
        
        Next sht
        
        MsgBox ("You entered " & entername & vbNewLine & "Sheet by this name is not available")
        
    Loop
    
End Sub

All the while, I have attempted to correct your code and explain the reasoning. For that reason, I have tried to keep as much of your code as possible and just steer you toward your goal. If I was writing this from scratch, I would use the approach suggested by Ike.

CodePudding user response：

To handle all usecases (no input given, existing sheetname given, non-existing sheetname given) - you can use this code:

Public Sub activateSheetByUserInput()
Dim pendworkbook As Workbook
Dim sht As Worksheet
Dim entername As String

Set pendworkbook = Workbooks("pend_app_new.xlsx")


retry:
entername = InputBox("Enter name", "Search Sheet")

If LenB(entername) = 0 Then
    Exit Sub
ElseIf tryGetWorksheetByName(pendworkbook, entername, sht) = True Then
    sht.Activate
Else
    'give the user the option to cancel the process
    If vbCancel = MsgBox("You entered " & entername & vbNewLine & "Sheet by this name is not available", vbCritical   vbRetryCancel) Then
        Exit Sub
    Else
        GoTo retry
    End If
End If
    
End Sub


'function returns true if worksheet was found - plus the ws itself

Private Function tryGetWorksheetByName(ByVal wb As Workbook, ByVal strName, ByRef sht As Worksheet) As Boolean

On Error Resume Next 'one of the rare cases where it is valid to use on error resume next

Set sht = wb.Worksheets(strName)
If Err = 0 Then tryGetWorksheetByName = True

On Error GoTo 0
End Function

CodePudding user response：

Another way would be to supply a list of valid sheet names and ask them to select one.

Add a userform and place a listbox on it. I've left the default names, but would be better to name the controls to something relevant.

Add this code to the form:

Private Sub UserForm_Initialize()

    Dim pendworkbook As Workbook
    Set pendworkbook = Workbooks("pend_app_new.xlsx")
    
    Dim shts() As Variant
    ReDim shts(0 To 0, 0 To pendworkbook.Worksheets.Count - 1)
    
    Dim x As Long
    For x = 1 To pendworkbook.Worksheets.Count
        shts(0, x - 1) = pendworkbook.Worksheets(x).Name
    Next x
    
    ListBox1.List = Application.WorksheetFunction.Transpose(shts)

End Sub

Private Sub ListBox1_Click()
    MsgBox "You clicked " & ListBox1.Value
End Sub