Prompt to Name New Sheet

Prompt to Name New Sheet

Have you ever seen this dialog?

rename sheet dialog

I’ve only seen it while running a complicated modeless form, forgetting the form was open, and then double-clicking a sheet tab to rename the sheet.

Anyways, the other day I was thinking about how often I create a new sheet and then rename it. Then I thought how cool it would be if every time I added a new sheet I was prompted to name it. Then I thought how that might actually be a pain in the butt, especially when running an addin that adds multiple sheets. So instead I wrote some code that adds a new sheet and immediately prompts for a name.

At the same time I came up with a better answer to the oft-asked question about adding a worksheet in VBA and getting a valid, unduplicated sheet name from the user.

Both of these gems require the dialog box pictured at the top of this page. Before I’d just considered it an odd reminder to close my modeless form, but now it became something I needed. My first step was to find it, which meant remembering how to show an Excel dialog from VBA and then finding the Rename Sheet one:

dialogs list

I searched the list looking for names starting with xlDialogRename, xlDialogSheet and xlDialogWorksheet, but no joy. I got a little nervous that my dialog wasn’t there. But searching through the whole list and trying the likely ones revealed that I was looking for xlDialogWorkbookName. It must have been a long day at the Excel factory when they named that one, but hey, close enough!

The Application.Dialogs object lets you display Excel’s built-in dialogs. It has only one method: Show. This 2003 MSDN reference is handy because it actually has a link to the arguments for each dialog type, in case you haven’t memorized them. (This information seems to be gone in Excel 2010 online and offline help):

dialogs list arguments

The xlWorkbookName dialog takes two optional arguments, the name of the sheet to rename and the suggested new name. The second argument is what shows in the dialog. If you don’t specify the arguments it uses the Activesheet and its name, respectively.

You’ll notice in the screenshot above that the Show method is boolean. If the user presses OK it returns true, if they cancel it returns false. At first I thought this would be helpful in ensuring a user renames a newly-added sheet. But you really just need to add the sheet, show the dialog, and then check if it’s been renamed:

Sub PromptForNewSheetWithName()
Dim DefaultSheetName As String

ActiveWorkbook.Worksheets.Add
DefaultSheetName = ActiveSheet.Name
Application.Dialogs(xlDialogWorkbookName).Show
If ActiveSheet.Name = DefaultSheetName Then
    MsgBox "You didn't name the new sheet." & vbCrLf & _
           "Processing cancelled", vbExclamation
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End If
End Sub

The answers I see on the web for how to prompt a user to name a new sheet all use an InputBox and involve a fair amount of validation for name length, duplicate names and illegal characters (and completely ignore the single quote issue). Using this dialog, you can let Excel do all that validation:

sheet name validation

As for my original idea, I now have a two-line subroutine that adds a sheet and displays the dialog. I use an OnKey command in my personal macro workbook to set it to a Ctrl-Shift-F11 shortcut.

Think of the time I’ll save!

3 thoughts on “Prompt to Name New Sheet

  1. Pingback: Excel Roundup 20150622 « Contextures Blog

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>