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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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,772
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,893
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top