Cancel button not working for input box

[SlinkRN]

Hi there, I know I'm probably just doing something stupid, but my cancel button doesn't do a thing to stop my input box. It's a long compicated macro so please be patient but I thought I'd better include the whole macro so you can find out what I'm doing wrong. Can anyone help?

Sub AddNewEmployee()
Dim strName As String
Dim strName2 As String
Call GoToRNSchedule
Call ChooseRange
Application.ScreenUpdating = False
Call UnprotectAllSheets
Selection.EntireRow.Insert 'insert new row in RN sheet
On Error GoTo canceled
strName = InputBox("Enter employee name: Last name, First name.", "Employee Name")
ActiveCell = strName 'enter new name in RN sheet
ActiveCell.AddComment 'add comment so that addcomment sub will work later
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:="" 'comment is blank for now
ActiveCell.Select
frmShift.Show 'allow for shift choice so will show up with correct shifts

ActiveWindow.ScrollWorkbookTabs Sheets:=20
Sheets.Add.Name = strName 'add new schedule sheet for employee
ActiveSheet.Move After:=Sheets(30)
Sheets(strName).Select
Range("A6").Select
Sheets("RN").Select
ActiveCell.Offset(0, 1).Resize(1, 31).Copy 'copy cells so can be linked to sched sheet
Sheets(strName).Select
ActiveSheet.Paste Link:=True
Range("I6:O6").Select
Selection.Cut Destination:=Range("A15:G15")
Range("Q6:W6").Select
Selection.Cut Destination:=Range("a24:g24")
Range("Y6:ae6").Select
Selection.Cut Destination:=Range("a33:g33")
Sheets("RN").Select
Range("C12:I12").Copy 'copy days of week
Sheets(strName).Select
Range("a4:g4").Select
ActiveSheet.Paste Link:=True
Range("a13:g13").Select
ActiveSheet.Paste Link:=True
Range("a22:g22").Select
ActiveSheet.Paste Link:=True
Range("a31:g31").Select
ActiveSheet.Paste Link:=True
Columns("a:g").ColumnWidth = 10
Columns("a:g").HorizontalAlignment = xlCenter
Range("c2").Select 'enter employee name to sched sheet
ActiveCell.Value = strName
Call GoToRNSchedule
ActiveCell.Offset(1, 0).Select
strName2 = ActiveCell.Value
Sheets(strName2).Select
ActiveSheet.Shapes("Button 2").Select 'copy command buttons for sched sheet
Selection.Copy
Sheets(strName).Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementLeft 120.75
Selection.ShapeRange.IncrementTop -12.75
Range("A1").Select
Sheets(strName2).Select
ActiveSheet.Shapes("Button 1").Select
Selection.Copy
Sheets(strName).Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementLeft -48.75
Sheets(strName2).Select
Range("I13:J34").Copy 'copy legend for sched sheet
Sheets(strName).Select
Range("I13:J34").PasteSpecial
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
ActiveWindow.DisplayZeros = False
Sheets("RN").Select
Range("c13:AG13").Copy 'copy dates for sched sheet
Sheets(strName).Select
Range("A5:AE5").Select
ActiveSheet.Paste Link:=True
Range("I5:O5").Select
Selection.Cut Destination:=Range("A14:G14")
Range("Q5:W5").Select
Selection.Cut Destination:=Range("A23:G23")
Range("Y5:AE5").Select
Selection.Cut Destination:=Range("A32:G32")
Range("B1").Select
Sheets("RN").Select
Call Sheet1.colormeblue
Call Sheet1.colormewhite
Call Sheet1.uncolormeblue
Call LinkSchedSheets
Call SortSheetsAlphabetically
Call ProtectAllSheets
Call gotomainpage
canceled:
Application.ScreenUpdating = True

End Sub


Is there just TOO MUCH stuff between "go to canceled" and "canceled"?
Thanks for your help, Slink

 

[dk]

Hi,

The standard VBA inputbox won't cause an error when you click cancel which is why your code is not working as you expect. The inputbox returns a zero-length string ( "" ) if you click cancel, so you need to check for this rather than an error.

Alternatively, Excel has its own Inputbox function available which returns False if cancel is pressed e.g.

Sub testofinput()
    Dim RetVal As Variant

    RetVal = Application.InputBox(prompt:="Enter text", Type:=2)

    If RetVal = False Then Exit Sub

    MsgBox "You entered " & RetVal

End Sub

 

[SlinkRN]

Thanks so much Dan! This sounds like just what I needed

 

[SlinkRN]

Hi again - that worked except is there a way to exit the main sub? The first input box is in the sub procedure "chooseRange" (which is called from the main sub "addNewEmployee"), so if they click on cancel on that input box, I need the entire "addNewEmployee" sub to end, not just the "chooseRange" sub. Is that do-able?

 

[SlinkRN]

Hi there! I found my answer by searching "exit main sub". I don't know how to paste a reference to that post but if you do that search and then look at the one posted at 09 Jan 2004 09:36 you'll see the answer. I LOVE THIS BOARD

 

[scuzz]

to exit the main sub, just use the code

Exit sub

That makes it jump automatically to End Sub