PDA

View Full Version : Run macro on cell text change



TomDuff
02-19-2009, 02:39 AM
Basically I have a macro which checks whether a cell is empty and if it is, it hides the row the cell is in. I want this to run every time the value of the cell (which contains a formula linked to another sheet) changes, how would I do this? I am a huge noob and have basically no knowledge of VBA at all...

Here is my macro:


Sub HideEmptyRowsB34()
Dim cell As Range
Rows("34").EntireRow.Hidden = False
For Each cell In ActiveSheet.Range("B34")
cell.EntireRow.Hidden = Len(cell.Text) = 0
Next cell
End Sub

Any help would be greatly appreciated!

Tom

Bob Phillips
02-19-2009, 03:02 AM
Private Sub Worksheet_Calculate()
Dim cell As Range
With Me.Range("B34")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

TomDuff
02-19-2009, 03:05 AM
Works like a charm, thanks mate!

TomDuff
02-19-2009, 08:47 AM
I'm trying to do this for multiple cells and their associated rows but it's very slow if it has to check every row and rehide them every time a change is made to one of the cells. I tried making each calculation only affect the row of the cell which has changed using this:

Private Sub Worksheet_Calculate()
Dim cell As Range
Rows("34").EntireRow.Hidden = False
With Me.Range("B34")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub
Private Sub Worksheet_Calculate()
Dim cell As Range
Rows("5").EntireRow.Hidden = False
With Me.Range("B35")
.EntireRow.Hidden = Len(.Text) = 0
End With
End Sub

but I get this message when I alter one of the cells:

"Compile Error:

Ambiguous name detected"

Does anyone know a way around this?

Bob Phillips
02-19-2009, 08:54 AM
You cannot have multiple Calculate subs, and it will check every time the worksheet recalculates, cannot be avoided I am afraid.

If the formula in B34/B35 is not that complex, and the precedent cells are static you could always check those cells on a change event.