need VBA help with Cancel on Input Box

[expresso]

Below is vba code which adds a new worksheet in the rightmost position and allows the user to name the worksheet. It works except if the user tries to click on the cancel button on the InputBox...the form simply reappears. I think I understand why this is occuring but have not been able to come up with an elegant or even inelegant solution...would appreciate some help. Thanks!

Sub AddUserSheets()
Dim LastSheet As Long
On Error Resume Next
ActiveWorkbook.Unprotect Password:="123"
'Add a new worksheet with generic name, eg. Sheet3
With ActiveWorkbook
LastSheet = .Sheets.Count
.Sheets.Add _
After:=.Sheets(.Sheets.Count), _
Type:="worksheet", _
Count:=1
End With
'Select the new worksheet
Sheets(LastSheet + 1).Select
'Let the user name the new worksheet
ActiveSheet.Name = InputBox("Name for new worksheet?")
'Keep asking for name if name already used or is invalid
Do Until Err.Number = 0
Err.Clear
ActiveSheet.Name = InputBox("Try Again!")
Loop
ActiveWorkbook.Protect Password:="123"
On Error GoTo 0
End Sub

 

[njimack]

After the line:

ActiveSheet.Name = InputBox("Name for new worksheet?")

Type:

If ActiveSheet.Name Is Nothing Then Exit Sub

This will still insert the new tab, but leave it's name untouched

 

[expresso]

helpful but not quite what I need...

Thanks for your suggestion. I thought I might expand on your idea thusly:

If ActiveSheet.Name Is Nothing Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Exit Sub
End If

but now, for a reason I cannot fathom, this will ALWAYS exit WITHOUT adding a worksheet.

 

[njimack]

Looking at your code, I'd say it DOES add a worksheet, but then immediately deletes it (ActiveSheet.Delete).

You can't see it happening because you've turned off alerts, so you aren't asked to confirm the deletion.

 

[Von Pookie]

Alternately, you can use the InputBox method instead (you're currently using the inputbox function--there is a difference).

Sub AddUserSheets()
Dim NewWs As Worksheet
Dim NewName As String
Dim chk As Boolean

'ActiveWorkbook.Unprotect Password:="123"

'Add a new worksheet with generic name, eg. Sheet3
chk = Empty

Set NewWs = Worksheets.Add
NewWs.Move after:=Sheets(Sheets.Count)

Do
    NewName = Application.InputBox("Name for new worksheet?", Type:=2)
    
    Select Case NewName
        Case Is = False 'cancel button clicked
            'delete added sheet
            Application.DisplayAlerts = False
            NewWs.Delete
            Application.DisplayAlerts = True
            Exit Sub 'end macro
        Case Is = "", " " 'ok is clicked with inputbox left blank
            chk = False
            MsgBox "You must enter a name.", vbOKOnly + vbExclamation
        Case Else
            'check if sheet name is already used
            If SheetExists(NewName) = True Then
                MsgBox "A sheet with this name already exists.", vbOKOnly + vbExclamation
                chk = False
            Else
                chk = True
            End If
    End Select
Loop Until chk = True    'Keep asking for name if name already used or is invalid


NewWs.Name = NewName

'ActiveWorkbook.Protect Password:="123"

End Sub
Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

 

[expresso]

add worksheet macro - solution

Your code works flawlessly...many thanks!