PDA

View Full Version : Solved: Code to check that a worksheet name from an inputbox is valid?



NukedWhale
01-22-2009, 01:17 PM
Hello,

I have an easy question (I think)

I'm writing a macro that adds a new worksheet to my workbook and uses the text from an input box to name the new worksheet.

I want to make sure the worksheet name provided is valid. Are there any other things that I need to check?

Here's what I have so far:

Sub CreateMatrixVector()
Dim NewWorksheetName As String
Dim DestinationSheet As Worksheet
Do While NewWorksheetName = "" Or Len(NewWorksheetName) > 31 Or NewWorksheetName = "Enter the new worksheet name HERE"
NewWorksheetName = InputBox("Please enter a worksheet name", "New Worksheet Name", "Enter the new worksheet name HERE")
Loop

Set DestinationSheet = Sheets.Add
ActiveSheet.Name = NewWorksheetName

End Sub

Bob Phillips
01-22-2009, 02:26 PM
Sub CreateMatrixVector()
Dim NewWorksheetName As String
Dim DestinationSheet As Worksheet
Do While NewWorksheetName = "" Or _
Len(NewWorksheetName) > 31 Or _
NewWorksheetName = "Enter the new worksheet name HERE"
NewWorksheetName = InputBox("Please enter a worksheet name", "New Worksheet Name", "Enter the new worksheet name HERE")
Loop

Set DestinationSheet = Sheets.Add
ActiveSheet.Name = ValidFileName(NewWorksheetName)

End Sub

Function ValidFileName(ByVal TheFileName As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[\\/:\*\?""<>\|]"
RegEx.Global = True
ValidFileName = RegEx.Replace(TheFileName, "")
Set RegEx = Nothing
End Function

NukedWhale
01-22-2009, 03:43 PM
This crashes when an incorrect worksheet name is specified.

How would I call the valid file name function within the while loop?

Bob Phillips
01-22-2009, 03:51 PM
What do you mean by incorrect?

NukedWhale
01-22-2009, 04:14 PM
My apologies, when I enter forbidden characters into the input box

i.e. ? /

Bob Phillips
01-22-2009, 04:37 PM
Works fine for me. I entered alp/*a\x.xls and it stripped the rubbish out nicely.

mikerickson
01-22-2009, 06:06 PM
This prevents the user from trying to name the new sheet with the same name as an existing sheet, as well as vetting it for general sheet name OKness.
Dim uiNewName As String, oldName As String, NameIsOK As Boolean

Do
uiNewName = Application.InputBox("Enter the name for the new sheet", Type:=2)
If uiNewName = "False" Then Exit Sub

oldName = ActiveSheet.Name
If uiNewName <> oldName Then
On Error Resume Next
ActiveSheet.Name = uiNewName
NameIsOK = (Err = 0)
ActiveSheet.Name = oldName
On Error GoTo 0
End If
Loop Until NameIsOK
ThisWorkbook.Sheets.Add
ActiveSheet.Name = uiNewName

NukedWhale
01-23-2009, 08:41 AM
XLD,

You're code worked correctly, I just referenced it incorrectly from within my larger macro. Many thanks!

Mikerickson,

I'll see how I can adapt and use your code along with xld's code. Thanks!

I'm marking this solved.