need VBA help with Cancel on Input Box

expresso

New Member
Joined
Aug 13, 2004
Messages
14
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

New Member
Joined
Aug 13, 2004
Messages
14
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Alternately, you can use the InputBox method instead (you're currently using the inputbox function--there is a difference).

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,798
Messages
5,574,372
Members
412,589
Latest member
ArtBOM
Top