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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.