Make Variables in Sheet Module Available in Regular Modules

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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:

VBA Code:
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):

VBA Code:
Sub a____SATURDAY__Start

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

End Sub


Thanks in advance for any insight!

~ DWR
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
There are many ways :

1- Change the macro as follows :
VBA Code:
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)
VBA Code:
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 :
VBA Code:
Sub a____SATURDAY__Start(ByVal ActiveWbkName As String)

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

End Sub
 

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
Yes. I would stick with option 3.

BTW, you can make it easier and more efficient as follows :
VBA Code:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call a____SATURDAY__Start(ActiveWorkbook)
End Sub
and then the module1 routine will look like this :
VBA Code:
Sub a____SATURDAY__Start(ByVal ActiveWbk As Workbook)
    ActiveWbk.Sheets(1).Range("C7") = "Start the Clock"
End Sub
 

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows
Thank you very kindly Jaafar - you have been a great help on this!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top