disable an inputbox

[lior03]

hello
i want to enable auser name a sheet he adds.he can decide not to.
suppose he wants to and an inputbox opens,if he changes his mind and press cancel the sheet adds anyway.this is not what i intended.
if he press select i want the macro to end.how caould it be doen?
[VBA]
Sub AddWorksheet6()
On Error Resume Next
Dim response As VbMsgBoxResult
Dim sht As Worksheet
response = MsgBox("Do you want to name the sheet ?" _
, vbQuestion + vbYesNoCancel, "add a new w.s")
Select Case response
Case vbCancel
Exit Sub
Case vbNo
Worksheets.Add
Case vbYes
Worksheets.Add().name = InputBox("enter sheet name", "sheet name", "name goes here")
End Select
MsgBox " new sheet added as requested - " & sht.name, vbInformation, "new sheet added" & " - " & Format(Now, "dd/mm/yy hh:mm:ss")
End Sub

[/VBA]
thanks

[lucas]

Your code does not add a sheet when you press cancel when I tried it.

[lior03]

how can i generally disable an inputbox if the user do not enter a value.

[lucas]

Do you mean if they click NO or if they click Cancel?
With your code when they click cancel it exits the sub and no sheet is added.
If they click NO then a sheet is added as you have instructed it to do....you could give it a default name using an if statement...

I'm sorry but I really do not understand the question.

[mikerickson]

 
Dim useResponce As String, newSheet As Worksheet
useResponce = Application.InputBox("New sheet name, please", Type:=2)
If useResponce = "False" Then
    MsgBox "Cancel pressed"
Else
    Rem Add a sheet
    Set Worksheet = Worksheets.Add
    If useResponce <> vbNullString Then newSheet.Name = useResponce
End If

[rory]

Moshe,
Here is a revised version of your code:
[VBA]Sub AddWorksheet6()
On Error Resume Next
Dim response
Dim sht As Worksheet
response = MsgBox("Do you want to name the sheet ?" _
, vbQuestion + vbYesNoCancel, "add a new w.s")
Select Case response
Case vbCancel
Exit Sub
Case vbNo
Set sht = Worksheets.Add
Case vbYes
response = InputBox("enter sheet name", "sheet name", "name goes here")
If Len(response) = 0 Then Exit Sub
Set sht = Worksheets.Add
sht.Name = response
End Select
MsgBox " new sheet added as requested - " & sht.Name, vbInformation, _
"new sheet added" & " - " & Format(Now, "dd/mm/yy hh:mm:ss")
End Sub
[/VBA]

[lior03]

hello all
i am working on a modified version.i added a word document with all instructions regarding this macro.i want to attach this as a help file.
[VBA]
Sub AddWorksheet55()
Dim helpfile
On Error GoTo canceled
Dim response As VbMsgBoxResult
Dim sht As Worksheet
response = MsgBox("Do you want to name the sheet ?", vbQuestion + vbYesNo + vbMsgBoxHelpButton, "add a new w.s", ThisWorkbook.Path & "\mouse.doc")
If response = vbYes Then
Set sht = Worksheets.Add
sht.name = InputBox("enter sheet name.", "please select a name:")
If Len(response) = 0 Then Exit Sub
ElseIf response = vbNo Then Exit Sub
Else: Exit Sub
End If
MsgBox " new sheet added as requested - " & sht.name, vbInformation, "new sheet added" & " - " & Format(Now, "dd/mm/yy hh:mm:ss")
canceled:
End Sub

[/VBA]
this macro do not work? .why?
how can i add a help file witha hlp extention? should i download some program?
thanks

[rory]

You need something like the HTML Help workshop. Unless you have a lot more stuff to put in it, it would be a lot of work for something as simple as this code!

PS You can download it from here