Trick a function to perform an action?
Trick a function to perform an action?
(OP)
Hello
Functions are designed only to return values... but I'm looking for a work around...
I want to create a user defined function that when evaluated to 0 the row is hidden (the row the function is in).
Perhaps this is a losing battle... ANY IDEAS?
Thanks for the help!!!
Have a great day!
Scott
Functions are designed only to return values... but I'm looking for a work around...
I want to create a user defined function that when evaluated to 0 the row is hidden (the row the function is in).
Perhaps this is a losing battle... ANY IDEAS?
Thanks for the help!!!
Have a great day!
Scott





RE: Trick a function to perform an action?
Private Sub Worksheet_Calculate()
Dim R As Range
For Each R In Me.UsedRange
If InStr(1, R.Formula, "MyFunc", vbTextCompare) > 0 Then
If R.Value = 0 Then
R.EntireRow.Hidden = True
Else
R.EntireRow.Hidden = False
End If
End If
Next R
End Sub
Now this is of course ugly and slow, but I can't think of anything more elegant right now...
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Trick a function to perform an action?
RE: Trick a function to perform an action?
Here's anohter way of doing this that is faster and eliminates the for looping. Of course you will need to add this to the OnChange Event of the Worksheet for each worksheet you would want to have the action on.
---------------
Private Sub Worksheet_Change(ByVal Target As Range)
cellVal = Range(Target.Address)
If cellVal = "0" Then
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If
End Sub
---------------