Pause to respond to message then run another macro

[letawellman]

Hi all!!
I'm new to posting here, but I have been visiting and searching for awhile now. I have a small problem that is probably very easy to fix.

I have "inherited" an Excel app that lets the user open a copy of the template via a macro. That part works fine. I edited the macro to immediately pop up a message box for the user to fill in a particular field, called "Quote Number".

Now what I want to do is, AFTER the user has clicked OK on the message box (and then hopefully entered the requested data) I want it to run another macro renaming the worksheet to what the user JUST ENTERED in that field.

Here's my code to copy the template and generate the message box:

Sub AddTemplate()
'
' Macro1 Macro
' Macro recorded 4/17/2006 by Dave Schroeder
'
' Keyboard Shortcut: Ctrl+q
'change tab name when updating price list
'
 
    Sheets("Template 04042011").Select
    Sheets("Template 04042011").Copy Before:=Sheets(1)
 
    Select Case True
    Case Range("B7") = ""
    MsgBox "Please enter the new Quote Number"
    End Select
 
    'RenameSheet
 
End Sub

And I found this code to rename the worksheet here on MrExcel:

Public Sub RenameSheet()
NewName = Range("B7").Value
ActiveSheet.Name = NewName
End Sub

Now, how do I get the code to pause between the first macro long enough to allow the user to enter the data into the cell (in this case, "B7")?

Thanks!! I love this site and I love MrExcel's books!!

Leta

 

[Jerry Sullivan]

Hi Leta, Welcome to the Board!

Msgboxes and Inputboxes both wait for the user response before proceeding.
Since you want to get information from the user, an Inputbox will be the better choice.

If you know that everything will go as expected, you could just do this...

Sub AddTemplate1()
    Dim varResponse As Variant
    Sheets("Template 04042011").Copy Before:=Sheets(1)
    varResponse = InputBox("Please enter the new Quote Number")
    Range("B7") = varResponse
    ActiveSheet.Name = varResponse
End Sub

Since things often don't go as expected, you could take your code a step further to handle these scenarios:

1. The template name doesn't exist. (If it is renamed in the workbook but not in the code).

2. The workbook already has a sheet name with the same name the user enters.

3. The user enters an invalid sheet name (like [1234] with brackets)

Sub AddTemplate2()
    Dim varResponse As Variant
    Dim strTemplate As String
    strTemplate = "Template 04042011"
    
    On Error Resume Next
    If IsError(Sheets(strTemplate).Activate) Then
        MsgBox "Template sheet: " & strTemplate & " was not found.", _
            vbExclamation, "Error"
        Exit Sub
    End If
    
    varResponse = Application.InputBox _
        ("Please enter the new Quote Number", , , , , , , 2)
    If varResponse = False Then Exit Sub
    If IsError(Sheets(varResponse).Activate) Then
        Sheets(strTemplate).Copy Before:=Sheets(1)
    Else
        MsgBox "A sheet named " & varResponse & " already exists", _
            vbExclamation, "Error"
        Exit Sub
    End If
    
    With ActiveSheet
        .Range("B7") = varResponse
        .Name = varResponse
        If .Name <> varResponse Then
            MsgBox "Unable to rename this sheet to: " & varResponse, _
                vbExclamation, "Error"
        End If
    End With
End Sub

 

[letawellman]

Thank you so much!!
By the time I got a response here, I had figured out about the input box, but putting in the "if error" part is definitely something I am going to add to mine.
And the way you changed the tab name is a little different from the way I did mine, I created a second macro after the input message.

I think your coding is cleaner and clearer than what I have, so I will probably recode mine.

Thanks again for the help!

Leta