Question on Events and Procedure - Workbook_Open

[USFengBULLS]

Hello All,

I am trying to have three cells filled on a particular sheet when a user opens the workbook every time. Here is the sub getting the values, which in in my module 1
Public Sub Project_Info()


Dim P_Number As Range, P_Name As Range, GC As Range


Set P_Number = Sheets("CO LOG").Range("B4")
Set P_Name = Sheets("CO LOG").Range("D4")
Set GC = Sheets("CO LOG").Range("GC") 'GC is merged cell of G4,H4 & I4


End Sub

When I go to my This Workbook Object and Procedure Open I have this coded
Private Sub Workbook_Open()
Sheets("NEW FORM-RESET").Activate


Call Project_Info
ActiveSheet.Range("B3") = P_Name
ActiveSheet.Range("B4") = P_Number
ActiveSheet.Range("B5") = GC


End Sub

When I run this Procedure/Event it does take me to that "New Form-Reset" (which is just a form) but it does not fill the three ranges with the contents of the cells from the other sheet that is in the Public Sub Project_Info () procedure that I wrote in my Module 1.

Anyone know what I am doing wrong? and if possible can you please explain a little more on how to call procedures from other procedures or an event?
I'm am practicing programming efficiency by writing multiple smaller procedures in the modules and then just using the Call function to run them in a an event or another procedure higher up...Thanks

 

[Eric W]

Your problem is the scope of your variables. You have it defined in Project_Info, but it's not available to Workbook_Open. Check this link to see how to make it available:

https://support.microsoft.com/en-us/help/141693/scope-of-variables-in-visual-basic-for-applications


Another, probably better, option is to pass the variables in the Call statement. Here's one link, although there's more to it than just this link:

https://www.thesmallman.com/pass-variables-between-macros