How can you make a macro global

[Jim Ashley]

I have many workbooks that all have the same type of information, how
can I make a macro global? Here is the macro:
Private Sub Worksheet_Calculate()
Dim varr As Variant
Dim i As Long
varr = Array(0, 1, 3)
For i = LBound(varr) To UBound(varr)
If Range("A40").Offset(varr(i), 0).Value = "" Then
Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = True
Else
Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = False
End If
Next
End Sub

Thanks, Jim



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Make it a workbook sheet calculate event that is

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

in ThisWorkbook module, and change the range references to include the sheet
object.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim varr As Variant
Dim i As Long
varr = Array(0, 1, 3)
For i = LBound(varr) To UBound(varr)
If sh.Range("A40").Offset(varr(i), 0).Value = "" Then
sh.Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = True
Else
sh.Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = False
End If
Next
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Do you mean global for a workbook? (It seems pretty specific to have it as a
global for all worksheets in all open workbooks.

Instead of putting the code under each sheet, you could put a version under the
ThisWorkbook module.

Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Dim varr As Variant
Dim i As Long

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet9": 'keep going
Case Else: Exit Sub
End Select

varr = Array(0, 1, 3)
For i = LBound(varr) To UBound(varr)
If Sh.Range("A40").Offset(varr(i), 0).Value = "" Then
Sh.Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = True
Else
Sh.Range("A40").Offset(varr(i), 0). _
EntireRow.Hidden = False
End If
Next i

End Sub

And that "select case" is one way to avoid doing these changes to unintended
sheets.

On the other hand, you could have a macro in a workbook that's always open (say
personal.xls) that you could run on demand that does the same thing. This would
not be automatic like your code that runs when the worksheet recalculates.