VBA InputBox need user to enter details if not loop back on itself-CodePudding

So I'm new to doing this !!! I have used the record Macro function and then expanded when necessary. what I need help with is having my input box require a text entry. if its blank have and error message and for it to return to the message box, if cancelled the new sheet be removed and go back to the beginning. here's what I'm working with.

Sub SAVE_TEMP_MAR()
'
' SAVE_TEMP_MAR Macro
'

'
    Sheets("JP PRN.").Select
    Sheets("JP PRN.").Copy Before:=Sheets(4)
    Sheets("JP PRN. (2)").Select
    Sheets("JP PRN. (2)").Name = ("TEMPORARY MAR")
    ActiveWindow.SmallScroll Down:=-21
    Range("A1:AF18").Select
    Selection.ClearContents
    Range("AG1").Select
    Sheets("Blank MAR").Select
    Range("A1:AF18").Select
    Selection.Copy
    Sheets("TEMPORARY MAR").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Blank MAR").Select
    Range("AG1").Select
    Sheets("TEMPORARY MAR").Select
    Sheets("TEMPORARY MAR").Name = InputBox("Enter new name for the MAR." & vbNewLine & vbNewLine & "Please use Resident initials and name of Medication ")
    Range("AG1").Select

Thanks in advance

CodePudding user response：

Edit: I just thought of an edgecase where the user might be stuck in an endless loop if they try to cancel after entering nothing, will update this once I tested it.

By using a variable and if-statements you will be able to check for those cases.

The following code could be optimised by a lot (recorded macros contain a lot of slow and useless code) but without the original workbook I won't try to optimise it, however it should do what you need:

Sub SAVE_TEMP_MAR()
'
' SAVE_TEMP_MAR Macro
'
    Dim userInput As String
    'Moving the InputBox up here so that if the user cancelles nothing happens.
retry:
    userInput = InputBox("Enter new name for the MAR." & vbNewLine & vbNewLine & _
                         "Please use Resident initials and name of Medication ")
    If StrPtr(userInput) = 0 Then
        'User cancelled, exit this subroutine
        Exit Sub
    ElseIf userInput = vbNullString Then
        'Input is empty, so go back to retry
        MsgBox ("You have to enter a new name!")
        GoTo retry
    End If
    Sheets("JP PRN.").Select
    Sheets("JP PRN.").Copy Before:=Sheets(4)
    Sheets("JP PRN. (2)").Select
    Sheets("JP PRN. (2)").Name = ("TEMPORARY MAR")
    Range("A1:AF18").Select
    Selection.ClearContents
    Range("AG1").Select
    Sheets("Blank MAR").Select
    Range("A1:AF18").Select
    Selection.Copy
    Sheets("TEMPORARY MAR").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Blank MAR").Select
    Range("AG1").Select
    Sheets("TEMPORARY MAR").Select
    Sheets("TEMPORARY MAR").Name = userInput
    Range("AG1").Select

End Sub

Basically this saves the userinput to a variable, which then gets checked with the two if statements that it didn't get cancelled and isn't empty, only then it proceeds. I used this answer as the template for adding that.

Also note that using GoTo statements is generally frowned upon, but I don't think that's gonna be a problem here.