Calling a sub routine from a startup Sub

[steveh8204]

I have created a Sub routine in the 'Workbook Open' section of the 'ThisWorkbook' object to open a custom dialog box when the Worksheet is open ("Do you want to generate a report?"). If the user selects yes the sub goes on to contain all the code needed to generate the report (still within this object). This bit works fine but the problem I've got is when the user selects no from the dialog box but then wants to run the routine it generates later on.

I am unfamiliar with how to re-run this sub routine. As the sub is called Private Sub Workbook_Open there is no way to call that as a macro (via a button or whatever) and if I just put Call (sub name from Module) in the Workbook Open sub it comes up with the following error:

Compile error:
Expected variable or procedure, not module

I have done a workaround by recreating all the code within a module but this doesn't seem very efficicent as I'm duplicating the code. I'm sorry if I haven't explained it very well I'm still getting used to the terminology.

Thanks in advance for any help.

 

[Rick Rothstein]

Put the "guts" of your current code in a subroutine in a general module, then call it from your Workbook_Open event code and also call it from your CommandButton event code (or from wherever else you want).

 

[My Aswer Is This]

Sort of like this:

Sub Hello()
'Modified  11/19/2018  6:00:26 AM  EST
MsgBox "Hello"
End Sub

Private Sub Workbook_Open()
Call Hello
End Sub

 

[steveh8204]

Thanks, I thought I'd done that but the problem was with the subroutine name. I was sure the error was because calling subs was done in a different way from here but your post prompted me to think I'd just called it the wrong way so I tried changing the name and it worked.

Thanks

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks, I thought I'd done that but the problem was with the subroutine name. I was sure the error was because calling subs was done in a different way from here but your post prompted me to think I'd just called it the wrong way so I tried changing the name and it worked.

Thanks