Options for triggering macro?

[bhop]

From this forum, I successfully deployed the code for autohiding (see below) rows. I have this on 25 different worksheets and when executing the macro on activation of each worksheet, it slows to a crawl.

What are my options for activating the macro to run as needed? Ideally, the cell range for each worksheet could be monitored and then execute the autohide macro only when changed. I am willing to entertain other suggestions as well. Thanks in advance.

VBA for AutoHide:

Private Sub workbook_close()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 101
Const LastRow As Long = 400

'< Set your columns that contain data >
Const FirstCol As String = "C"
Const LastCol As String = "G"
'*****************************

ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False

For HiddenRow = FirstRow To LastRow

'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

[mikerickson]

A WorkBook_Close routine should be in the ThisWorkbook code module, not 25 sheet code modules.

You might want to put those routines in each sheet's _Deactivate event.

[bhop]

Ahhh...that solved the speed issue. Thanks so much.

I am having difficulty on getting the event to execute properly. A confounding factor is that the workbook links to external data. When I go into VBA and execute the macro, all works well.

I could use suggestions on how to execute without opening VBA, such as assigning to a function key. I have searched around but came up empty.

Thanks for the vine.

[mikerickson]

1) Get a button from the Forms menu, put it on a sheet, right click and Assign Macro. (The Forms menu button is more easily assigned to a macro than those from the ToolBox.)

2) Go to the Macros window, press Options and assign the macro to a key.

3) Go to the Macros window, select the macro and press Run.

Just about anything is prefferable to opening the VBEditor in order to run a macro.

If you have difficulty finding these things, post the version of Excel that you are using. Microsoft ruined changed things with 2007.

[bhop]

Works perfectly. Thanks again.

[mikerickson]

You're welcome.