MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Attaching Macros to Message Boxes


Posted by Sam on June 19, 2001 12:57 PM

How would I attach a macro to a msgbox so that if the user clicks OK the macro is run. This is what I have so far but I'm not sure how to run the macro.

Sub DisplayMessageBox()
Dim iYesNo As Integer

iYesNo = MsgBox("Another Macro will now run", vbYesNo)

If iYesNo = vbYes Then

-???

Else
Exit Sub
End If
End Sub

Thanks in advance.
Sam


Posted by Damon Ostrander on June 19, 2001 1:10 PM

Hi Sam,

Just put the name of the macro in your code in place of the "-???". It's as simple as that! The macro you call has to be in a Macro Module (not in an event code area), but it can be in any module.

Example: suppose you have a macro named Macro1 defined by code that looks like

Sub Macro1()
...
End Sub

Then your code could be:

Sub DisplayMessageBox()
Dim iYesNo As Integer

iYesNo = MsgBox("Another Macro will now run", vbYesNo)

If iYesNo = vbYes Then Macro1

End Sub

But the macro could just as easily have been named Sam or Sams_macro_that_calculates_lots_of_amazing_things
.

Good luck.

Posted by Sam on June 19, 2001 1:41 PM

Thanks that did it.