Help with Inputbox Function

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Where do I set vbYesNo in a Inputbox? What I am trying to
accomplish is if the user clicks Cancel then it stops the
macro. Is it just as simple as putting Cancel as Boolean
within the ()?

Sub Paste_Macro()

On Error GoTo ErrorHandler
Dim StrDate As String

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
StrDate = InputBox("What is the date?", "Date Needed")
If StrDate = vbCancel Then Exit Sub
ActiveCell.FormulaR1C1 = StrDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(1, 1).Select
ActiveWorkbook.Save
Exit Sub

ErrorHandler:
MsgBox Err.Number & " " & Err.Description

End Sub
 
Eric,

InputBox doesn't return any of the vbYesNo type constants.
Instead, try something like

Dim Res As String
Res = InputBox("Enter something")
If StrPtr(Res) = 0 Then
MsgBox "User hit cancel"
ElseIf Len(Res) = 0 Then
MsgBox "User clicked OK with no input"
Else
MsgBox "User entered: " & Res
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Eric,

Try This

StrDate = InputBox("What is the date?", "Date Needed")
If CloseMode = vbFormControlMenu Then
Exit Sub
End If

Dan E
 
Tom,

Test it, it works . . .

Paste this code in a macro

StrDate = InputBox("What is the date?", "Date Needed")
If CloseMode = vbFormControlMenu Then
Exit Sub
End If

Range("A1").Value = "Bill"

Click cancel and i'll bet you that A1 isn't Bill.

Dan E
 
Back
Top