Help with Inputbox Function
 

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

Help with Inputbox Function
 

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



"Eric" wrote in message
...
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

Help with Inputbox Function
 

Eric,

Try This

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

Dan E

"Eric" wrote in message
...
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

Help with Inputbox Function
 

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

"Tom Ogilvy" wrote in message
...
Where would closemode get its value from. I have only seen it in the
queryclose event of a userform where it is set by code internal to the
msforms library and passed into the event as an argument.

To the best of my knowledge, as shown, closemode would always be an empty
variable.

Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)


vbFormControlMenu: The user has chosen the Close command from the
Control menu on the UserForm.


Regards,
Tom Ogilvy


"Dan E" wrote in message
...
Eric,

Try This

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

Dan E

"Eric" wrote in message
...
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

Help with Inputbox Function
 

Nevermind,

HaHa

Closemode was just 0 so it appeared to work, my mistake???

"Tom Ogilvy" wrote in message
...
Where would closemode get its value from. I have only seen it in the
queryclose event of a userform where it is set by code internal to the
msforms library and passed into the event as an argument.

To the best of my knowledge, as shown, closemode would always be an empty
variable.

Private Sub UserForm_QueryClose(cancel As Integer, closemode As Integer)


vbFormControlMenu: The user has chosen the Close command from the
Control menu on the UserForm.


Regards,
Tom Ogilvy


"Dan E" wrote in message
...
Eric,

Try This

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

Dan E

"Eric" wrote in message
...
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