Make Variables in Sheet Module Available in Regular Modules

[DougRobertson]

Hello,

I have created a Timesheet Workbook in Excel 2019, that starts or stops the clock by firing a macro when a specific cell is clicked. That macro lives in the Sheet1 module, and the Sub is named:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Immediately upon the macro firing and at the top of the procedure, I use the following code to ascertain the name of the Timesheet workbook:

iActiveTimesheet = ActiveWorkbook.Name

I then Call a procedure in a regular module to continue the process of starting or stopping the clock. Is there a way to have the contents of the variable "iActiveTimesheet" available when the macro enters the other regular modules?

A simplified version of the code is as follows:

Sheet1 module:

Public iActiveTimesheet As String

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

iActiveTimesheet = ActiveWorkbook.Name

Call a____SATURDAY__Start

End Sub

Module1 (in Modules folder - same workbook):

Sub a____SATURDAY__Start

Workbooks(iActiveTimesheet).Sheets(1).Range("C7") = "Start the Clock"

End Sub

Thanks in advance for any insight!

~ DWR

 

[Jaafar Tribak]

There are many ways :

1- Change the macro as follows :

Sub a____SATURDAY__Start()
    ThisWorkbook.iActiveTimesheet).Sheets(1).Range("C7") = "Start the Clock"
End Sub

or
2- Simply declare the variable as Public in Module1
or
3- Pass the variable as an argument to the a____SATURDAY__Start routine : (This is the prefered method)

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Dim iActiveTimesheet As String
   
    iActiveTimesheet = ActiveWorkbook.Name
   
    Call a____SATURDAY__Start(iActiveTimesheet)

End Sub

and then the module1 routine will look like this :

Sub a____SATURDAY__Start(ByVal ActiveWbkName As String)

    Workbooks(ActiveWbkName).Sheets(1).Range("C7") = "Start the Clock"

End Sub

 

[DougRobertson]

This is great Jaafar!

However with your suggestion # 2, I'd like that variable to also be available in 6 more regular modules, but I get the "ambiguous" error message. Is there a way around that, or should I just go with suggestion # 3?

 

[Jaafar Tribak]

Yes. I would stick with option 3.

BTW, you can make it easier and more efficient as follows :

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call a____SATURDAY__Start(ActiveWorkbook)
End Sub

and then the module1 routine will look like this :

Sub a____SATURDAY__Start(ByVal ActiveWbk As Workbook)
    ActiveWbk.Sheets(1).Range("C7") = "Start the Clock"
End Sub

 

[DougRobertson]

Thank you very kindly Jaafar - you have been a great help on this!