How to select a sheet from a user input box-CodePudding

I am having trouble coding this VBA code. I need the code to ask the user if they want data or a graph. If the user selects Yes From there, I need to the code to look at the selected input and see if that input is a valid sheet name. If not, the input box will display again until valid sheet name. If the sheet is valid, then I need the sheet to be selected or show up whenever the user enters a valid value. I hope that makes sense.

For example, if the user enters (10-1) that is a valid sheet or (1-1) valid sheet but if it is (14-1) or (a-a) that is not a valid sheet.

Note I have not gotten to the graphing part yet, so do not worry about if the user selects no yet. Can someone get me in the correct direction?

Sub InputValidation()
    Dim str As String
    Dim inp As String
    Dim ws As Worksheet

    
    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion   vbYesNo)
    
    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
            Exit Sub
            End If
        ElseIf inp = "#-#" Or "##-#" Then
                If Sheets(ws).Name = inp Then
                    Worksheets(inp).Activate
                End If
        Else
            MsgBox "This load and test cannot be found"
    
    If str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
            Exit Sub
            End If
        End If
    End If
        
End Sub

CodePudding user response：

Please make sure you close all you if statements when using indentation? The way you have now set it implies that the last End if is covering the entire str = vbYes scope, i.e. if str = vbNo nothing happens?

Sub InputValidation()
    Dim str As String
    Dim inp As String
    Dim ws As Worksheet

    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion   vbYesNo)

    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
                Exit Sub
            'End If
        ElseIf inp = "#-#" Or "##-#" Then
            If Sheets(ws).Name = inp Then
                Worksheets(inp).Activate
            End If
        Else
            MsgBox "This load and test cannot be found"
        End If '=> added this End If as it will otherwise skip to end of sequence
    ElseIf str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            If MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
                Exit Sub
            End If
        End If

End Sub

I also think your below line is incorrect?

ElseIf inp = "#-#" Or "##-#" Then

It will give a Type Mismatch error? (Error 13) You could contemplate using a regex for this assessment or simply use something like the below?

ElseIf inp like "*-*" Then

Which would also do the trick?

The subsequent statement tries to select the Worksheet but that won't work as you have not set the ws object anywhere? So the below line is incorrect in many ways:

If Sheets(ws).Name = inp Then

Please see below code that will give you a good base to start from? This would also get rid of the unnecessary inp = "#-#" comparison

Private Function SheetExists(name As String) As Boolean
    
    SheetExists = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.name = name Then SheetExists = True: Exit Function
    Next
    
End Function

Private Function ConfirmEndSub()
    
    ConfirmEndSub = MsgBox("Do you really want to QUIT", vbYesNo   vbQuestion)
    If ConfirmEndSub = vbYes Then
        MsgBox "Thank You Goodbye"
    End If
    
End Function

Sub InputValidation()
    Dim str As String
    Dim inp As String
    Dim ws As Worksheet

    str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion   vbYesNo)

    If str = vbYes Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            Reply = ConfirmEndSub
            If Reply = vbYes Then Exit Sub
        ElseIf SheetExists(inp) Then
            Worksheets(inp).Activate
        Else
            MsgBox "This load and test cannot be found"
        End If
    ElseIf str = vbNo Then
        inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
        If StrPtr(inp) = 0 Then
            Reply = ConfirmEndSub
            If Reply = vbYes Then Exit Sub
        End If
    End If
End Sub

This leaves entirely out of consideration that depsite the initial vbYesNo answer the user will always get the input box? Not sure if that is the intention, but alas that is how it was written.

One other consideration is that if the user selects not to Quit, the call is not returning to the request for input? => I.e. it is still ending the routine...