Pass variables from Worksheet_Calculate sub to Module
 

I have a bit of code that is triggered by the Worksheet_Calculate
event. I want to use this code of several worksheets but would prefer
not to repeat it in each Worksheet_Calculate event object. I thought
that at minimum, I could set the ranges specific to a particular sheet
in the Worksheet_Calculate event then hop over to module, and run the
code specific to the ranges. The ranges don't seem to pass to the
module.

Here's my Worksheet code. Any ideas why the values won't pass?

Public intShowing As Integer
Public w As Worksheet
Public rSheet As Range
Public rShow As Range
Public rData As Range
Public rCrit As Range
________________________________________________

Private Sub Worksheet_Calculate()


rSheet = ActiveWorksheet
rShow = rSheet.Range("RecordsShowing")
rData = rSheet.Range("Process_Data")
rCrit = rSheet.Range("FiltersCriteria_data")

ShowFilter 'Pass the range variables and run the ShowFilter sub from a
module

End Sub

________________________
Sub ShowFilter()
Msgbox "rSheet = " & rSheet
...etc

End Sub

Pass variables from Worksheet_Calculate sub to Module
 

The code in each sheet only responds to events from that sheet. If you want
something more global then use ThisWorkbook events...

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Determine if sheet need macro run on it
End Sub
--
HTH...

Jim Thomlinson


"John Michl" wrote:

I have a bit of code that is triggered by the Worksheet_Calculate
event. I want to use this code of several worksheets but would prefer
not to repeat it in each Worksheet_Calculate event object. I thought
that at minimum, I could set the ranges specific to a particular sheet
in the Worksheet_Calculate event then hop over to module, and run the
code specific to the ranges. The ranges don't seem to pass to the
module.

Here's my Worksheet code. Any ideas why the values won't pass?

Public intShowing As Integer
Public w As Worksheet
Public rSheet As Range
Public rShow As Range
Public rData As Range
Public rCrit As Range
________________________________________________

Private Sub Worksheet_Calculate()


rSheet = ActiveWorksheet
rShow = rSheet.Range("RecordsShowing")
rData = rSheet.Range("Process_Data")
rCrit = rSheet.Range("FiltersCriteria_data")

ShowFilter 'Pass the range variables and run the ShowFilter sub from a
module

End Sub

________________________
Sub ShowFilter()
Msgbox "rSheet = " & rSheet
...etc

End Sub

Pass variables from Worksheet_Calculate sub to Module
 

Thanks, Jim. I'll give that a shot. Sounds like it would be a better
approach anyway.

- John


Jim Thomlinson wrote:
The code in each sheet only responds to events from that sheet. If you want
something more global then use ThisWorkbook events...

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Determine if sheet need macro run on it
End Sub
--
HTH...

Jim Thomlinson


"John Michl" wrote:

I have a bit of code that is triggered by the Worksheet_Calculate
event. I want to use this code of several worksheets but would prefer
not to repeat it in each Worksheet_Calculate event object. I thought
that at minimum, I could set the ranges specific to a particular sheet
in the Worksheet_Calculate event then hop over to module, and run the
code specific to the ranges. The ranges don't seem to pass to the
module.

Here's my Worksheet code. Any ideas why the values won't pass?

Public intShowing As Integer
Public w As Worksheet
Public rSheet As Range
Public rShow As Range
Public rData As Range
Public rCrit As Range
________________________________________________

Private Sub Worksheet_Calculate()


rSheet = ActiveWorksheet
rShow = rSheet.Range("RecordsShowing")
rData = rSheet.Range("Process_Data")
rCrit = rSheet.Range("FiltersCriteria_data")

ShowFilter 'Pass the range variables and run the ShowFilter sub from a
module

End Sub

________________________
Sub ShowFilter()
Msgbox "rSheet = " & rSheet
...etc

End Sub