Triggering Macro to Run

[Fujirich]

I previously asked about a row hiding problem, as expressed in the link below:

http://www.mrexcel.com/board2/viewtopic.php?t=72813

I've found a solution to achieve my row-hiding needs, but I need to know how to add to this code to trigger the macro to run. Here's the code:

Sub HideRowIfZero()
'Hides row(s) with zero so chart does not show zero or it's coresponding label entry.

For Each c In Worksheets("Summary & Graphs").Range("B506:B519")
If c.Value = 0 Then
c.Activate
Selection.EntireRow.Hidden = True

End If

Next c

For Each c In Worksheets("Summary & Graphs").Range("B506:B519")
If c.Value > 0 Then
c.Activate
Selection.EntireRow.Hidden = False

End If

Next c

End Sub

What I need to add is running this anytime a change is made on another sheet in the workbook range: All Accounts!A19:All Accounts!M318

What should I add to this code to make this work?

Thanks!

Rich

 

[Legacy 21301]

If cells B506:B519 in "Summary & Graphs" contain formulas, then you could put this in the "Summary and Graphs" Sheet module :-

Private Sub Worksheet_Calculate()
Dim rng As Range, c As Range
Set rng = Worksheets("Summary & Graphs").Range("B506:B519")
rng.EntireRow.Hidden = False
For Each c In rng
If c.Value = 0 Then c.EntireRow.Hidden = True
Next
End Sub

It would be triggered by any calculation on "Summary & Graphs".

Alternatively, you could put this in the "All Accounts" Sheet module :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set rng = Worksheets("Summary & Graphs").Range("B506:B519")
rng.EntireRow.Hidden = False
For Each c In rng
If c.Value = 0 Then c.EntireRow.Hidden = True
Next
End Sub

It could be restricted to be triggered only when there is a chenge to the relevant cells in "All Accounts".
For example, if the relevant cells were C1:C20 :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRng As Range
Dim rng As Range, c As Range
Set sRng = Worksheets("All Accounts").[C1:C20]
If Not Intersect(Target, sRng) Is Nothing Then
    Set rng = Worksheets("Summary & Graphs").[B506:B519]
    rng.EntireRow.Hidden = False
    For Each c In rng
    If c.Value = 0 Then c.EntireRow.Hidden = True
    Next
End If
End Sub

 

[HalfAce]

Hi Rich,
Try this to begin with and see if it's a start. You'll likely have to add a little bit to it, such as where you want to end up and such, but it kicks off your code if a change is made in the range A19:M318 of the All Accounts sheet.
You'll want to put this in the worksheet module of the All Accounts sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A19:M318]) Is Nothing Then Exit Sub
Sheets("Summary & Graphs").Select
HideRowIfZero
End Sub

This help?
Dan

 

[HalfAce]

Ponsy Nob.,
I like that first routine, the worksheet_calculate. That's slick.

 

[Fujirich]

Ponsy Nob. & HalfAce - firstly, thanks so much for the replies!

I agree with HalfAce, Ponsy's worksheet_calculate solution is pretty slick. Unfortunately, when I try it in my workbook, putting that code in the "Summary & Graphs" worksheet module, I get some strange results.

When I make a change on any of the cells of the first page that would trigger new values in the range B506:B519, the maco seems to keep executing on, and on, and on.... seemingly caught in an endless loop.

If I open a new workbook, set it up properly, the code runs perfectly.

I must be getting some conflict with another macro in the workbook. I have Worksheet_Change and Worksheet_Calculate routines in the module of the first page (All Accounts). Some conflict must exist?...

Just for the possibility of finding the problem, here's my code from the worksheet module for All Accounts (first page in the workbook):

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.ScreenUpdating = False

If ActiveCell.Row >= 18 And ActiveCell.Row <= 318 And ActiveCell.Column = 9 Then
    If ActiveCell.Value = "5: Gained" Or ActiveCell.Value = "6: Not Gained" Then
        ActiveCell.Offset(0, 6).Value = Format(Date, "mm/dd/yyyy")
    Else
        ActiveCell.Offset(0, 6).Value = ""
    End If
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Begin MS Support
Private Sub Worksheet_Change(ByVal Target As Range)
'Disable the Screen Updating
Application.ScreenUpdating = False

'Use Traget Method for Change Events
If Target.Row >= 19 And Target.Row <= 318 And Target.Column = 1 Then
    Target.Offset(0, 10).Value = Format(Date, "mm/dd/yyyy")
End If

'Write the Current Date in Range L16
Application.EnableEvents = False
Range("$L$16").Value = Format(Date, "mm/dd/yyyy")
Application.EnableEvents = True

'Enable the Screen Updating
Application.ScreenUpdating = True
End Sub

Any ideas?

 

[HalfAce]

If I understand your original post, (which I may not ), you still want the HideRowIfZero routine to run whenever there's a change made in the range A19:M318 of the All Accounts sheet (?) If this is right, how about changing the section of your existing Worksheet_Change code:
'Use Traget Method for Change Events
If Target.Row >= 19 And Target.Row <= 318 And Target.Column = 1 Then
Target.Offset(0, 10).Value = Format(Date, "mm/dd/yyyy")
End If
to read like this instead:
'Use Traget Method for Change Events
If Target.Row >= 19 And Target.Row <= 318 And Target.Column = 1 Then
Target.Offset(0, 10).Value = Format(Date, "mm/dd/yyyy")
Sheets("Summary & Graphs").Select
HideRowIfZero
Sheets("All Accounts").Select
End If

I admit I haven't tested this out but it looks like it might work.

Dan

 

[Fujirich]

HalfAce - After a bit of a break, I came back and saw your comments. Putting back that original code, and adding to the Worksheet_Change in the manner you mentioned seems to have worked perfectly.

Thanks so much for the insight and help on this! Folks like you make this such a wonderful place.