InputBox to enter details, if no details loop back on itself

Question

I need my InputBox to require a text entry.
If its blank have and error message and return to the InputBox.
If cancelled the new sheet be removed and go back to the beginning.

I used the record Macro function.

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

Cook IT · Accepted Answer · 2022-05-20T20:38:30.287

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.~~ After proper testing it turns out this isn't a problem.

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.

Thank you very much nice and simple I knew it had to be in if statement somewhere but never occurred to me to shift it as a variable at the top — Universaloneill, May 20 '22 at 05:34

InputBox to enter details, if no details loop back on itself

1 Answers1