PDA

View Full Version : Solved: Message box code



BENSON
01-17-2007, 07:17 AM
The code below shows a message box if a criteria is not met ( In this case cell V5 being blank). It allows the user the option to continue or not . I need help with further code so that if cell V5 is not blank the macro will run (ie Copy and paste a selection of data) without showing any message box.

Thanks for any help you can offer

Sub Button4_Click()
'
' Button4_Click Macro
If IsEmpty(Range("v5").Value) Then

Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("WARNING NO CLOSING STOCK HAS BEEN ENTERED ,IF YOU CONTINUE THERE WILL BE NO OPENING FOR NEW REPORT ?", vbQuestion + vbYesNo, "Decision time!")
If strAnswer = vbYes Then



'
Range("V5:V240").Copy Range("C5")

Range("D5:Q240,V5:W240").Select
Range("V5").Activate ' Not sure why this is here
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearComments
End If
End If
End Sub

austenr
01-17-2007, 07:24 AM
Hi,

Try this:


Sub Button4_Click()
'
' Button4_Click Macro
If IsEmpty(Range("v5").Value) Then

MsgBox("WARNING NO CLOSING STOCK HAS BEEN ENTERED ,IF YOU CONTINUE THERE WILL BE NO OPENING FOR NEW REPORT ?", vbQuestion + vbYesNo, "Decision time!")
Else
Range("V5:V240").Copy Range("C5")
Range("D5:Q240,V5:W240").Select
Range("V5").Activate ' Not sure why this is here
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearComments
End If

End Sub

Charlize
01-17-2007, 07:24 AM
Reverse the logic.
Sub testvalue()
If Not IsEmpty(Range("V5").Value) Then
Range("V5:V240").Copy Range("C5")
Else
MsgBox ("Nothing to copy")
End If
End Sub
Charlize

CBrine
01-17-2007, 07:24 AM
Nevermind, I misunderstood what you were looking for.

mdmackillop
01-17-2007, 07:27 AM
Sub Button4_Click()
'
' Button4_Click Macro
If IsEmpty(Range("V5").Value) Then
Dim strAnswer As VbMsgBoxResult
strAnswer = MsgBox("WARNING NO CLOSING STOCK HAS BEEN ENTERED, " & _
"IF YOU CONTINUE THERE WILL BE NO OPENING FOR NEW REPORT ?", _
vbQuestion + vbYesNo, "Decision time!")
If strAnswer = vbYes Then DoCopy
Else
DoCopy
End If
End Sub

Sub DoCopy()
Range("V5:V240").Copy Range("C5")
With Range("D5:Q240,V5:W240")
.ClearContents
.ClearComments
End With
Application.CutCopyMode = False
End Sub

BENSON
01-17-2007, 08:13 AM
Thank you all for your help you guys are great ,mdmackillop your code is perfect