CODEWITHSUNDEEP
excelvba
Universaloneill
Universaloneill

Reputation: 25

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

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

Upvotes: 0

Views: 124

Answers (1)

Cook IT
Cook IT

Reputation: 88

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.

Upvotes: 1

Related Questions