YES or No Options on a MsgBox using VBA

[Celtic_Avenger]

I have created a msgbox that is activated by an active control button
using the following code......

MsgBox Range("AC6") & Range("AD6") & Range("AE6") & Chr(13) & "Woul
you like to set your Sales Target to this value?", vbYesNo, "MINIMU
ACCEPTABLE STANDARDS"

This brings up a box that shows relevant information, and then asks
yes - no question.

If the user selects "Yes", I would like to run the followin
code..........

Range("J3:K3").Select
ActiveCell.FormulaR1C1 = Range("AE6")

but do nothing and close the msgbox if "No" is pressed.

Could some kind soul show me how to join the two codes.

Thanks.

Celtic_Avenger
:confused

 

[Frank Kabel]

Hi
assign the result of the messagebox to a variable and evaluate this.
e.g.
dim res
res=msgbox "my message"
if res = vbyes then
'do something
else
'do something else
end if

--
Regards
Frank Kabel
Frankfurt, Germany

"Celtic_Avenger >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[JE McGimpsey]

One way:

Dim nResult As Long
nResult = MsgBox("hello", vbYesNo)
If nResult = vbYes Then _
Range("J3").Value = Range("AE6").Value

Note that you almost never have to select a range. Working with the
range object directly makes your code smaller, faster, and, IMO, easier
to maintain.

 

[Celtic_Avenger]

Thanks both.

have re-writen code to..............

Private Sub CommandButton18_Click()
Dim res
res = MsgBox(Range("AC6") & Range("AD6") & Range("AE6") & Chr(13)
"Would you like to set your Sales Target to this value?", vbYesNo
"MINIMUM ACCEPTABLE STANDARDS")
If res = vbYes Then
Range("J3:K3").Select
ActiveCell.FormulaR1C1 = Range("AE6")
End If
End Sub

This now works a treat.

Thanks again!

Celtic_Avenger
: