How to embed a macro in a cell formula

Bruce,

Unfortunately a function can't affect anything in the Excel environment. A function can only return a value or display a message box. The way to hide a row based on the contents of a cell is to use a worksheet event e.g.

Right click a worksheet tab and choose View Code. Use something like this:-

Private Sub Worksheet_Calculate()
'Example : If the value in A1 is greater than 10 then
'hide rows 10-15, else unhide them.
If Me.Range("A1").Value > 10 Then
Me.Range("A10:A15").EntireRow.Hidden = True
Else
Me.Range("A10:A15").EntireRow.Hidden = False
End If
End Sub

HTH,
D

D,

If I've got this correct,
Cannot change it within formula, so go to code behind the sheet.

To do it on a row by row basis over a whole column I would just loop from rownum 1 to maxrows (or something like that) and rather than hiding a range, just the single row.

Won't this evaluate ALL the time. Is there a method to have this code associated ONLY with the cells in a given column and fire only when the value of the cell changes.

Depends on whether the cell you're evaluating is typed in or it's a formula. If it's typed in directly then you could try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value > 10 Then
Range("A10:A15").EntireRow.Hidden = True
Else
Range("A10:A15").EntireRow.Hidden = False
End If
End If
End Sub

However, if the cell you're evaluating is a formula then you'll need to use the Worksheet_Calculate event. As you probably know, this doesn't return any information about which cells have changed. Therefore, the code will run every time the worksheet is calculated. Unless the code is complex then I doubt you'd even notice the macro being ran.

D

Thanks,
It's a formula. I'll work on the Worksheet_calculate event. 'Haven't been there before. There's an awful lot under the hood of this application.

Appreciate the lead and your time.

Bruce