Pass variables from Worksheet_Calculate sub to Module

[John Michl]

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

[Jim Thomlinson]

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
>
>

[John Michl]

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
> >
> >