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