Create Message Box Upon Open

[leewoo5]

Hi all,

I would like to create a macro to prompt the following message upon opening the workbook:

"Create New Report?"

Followed by a Yes / No answer.

If Yes is selected I would like it to run the following macro:

Click_NewReport

If no is selected then it just opens the workbook as normal.

I appreciate any help that can be given with this.

Many thanks,

 

[L. Howard]

Try this copied to the ThisWorkbook code module.

Howard

Private Sub Workbook_Open()
Dim Msg, ans, Cancel
 
        'msg = "Do you want to do this? "
        'msg = msg & "First line of message," & vbCr & "Second line of nessage."
        
        Msg = "Create New Report?"
        
        
        ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        
        Select Case ans

            Case vbYes
                'MsgBox "Yes was clicked"
                Click_NewReport

            Case vbNo
                'MsgBox "No was clicked"
                Exit Sub

            Case vbCancel
                'MsgBox "Cancel was clicked"
                Cancel = True
                Exit Sub

          End Select

End Sub

Sub Click_NewReport()
  MsgBox "The sub ""Click_NewReport"" was called"
End Sub

 

[leewoo5]

Thanks for your help, the macro works whilst testing it in the VBA screen but does not prompt when opening the workbook?

Many thanks,

 

[L. Howard]

Did you copy to the ThisWorkbook module or a worksheet module?

Howard

 

[leewoo5]

My apologies, works perfectly.
I really appreciate the help!

Many thanks,

 

[L. Howard]

Okay, great. You're welcome.

Howard