Cancel my Inputbox

**nickmax1** · 08-21-2012, 10:37 AM

I have a simple Inputbox macro that creates a new sheet called whatever is unput. When hitting the cancel button though I get an error (object defined 1004 job)

How do I solve this?

Sub NewS()

Dim Sheetname As String
Worksheets("Template").Visible = True
Sheetname = InputBox("Please enter new sheet name, Use format MMM YYYY")
ActiveWorkbook.Sheets("Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheetname
ActiveSheet.Range("A1").Value = Sheetname
MsgBox "Dont forget to fill in Budget and Forecast values"
Worksheets("Template").Visible = False
End Sub

**patel45** · 08-21-2012, 10:47 AM

change so

If Sheetname = "" Then Exit Sub
ActiveSheet.Name = Sheetname

**Mordred** · 08-21-2012, 10:47 AM

Does the following do what you need?

Sheetname = InputBox("Please enter new sheet name, Use format MMM YYYY")
If Sheetname = vbNullString Then
    Exit Sub
End If

**nickmax1** · 08-21-2012, 11:05 AM

works brilliantly thanks!

---------- Post added at 04:05 PM ---------- Previous post was at 04:03 PM ----------

also is there a way to force the input to be MMM YYYY if there is anything else it comes up with a message saying "Wrong input" or something>?