Trick a function to perform an action?

[ByDesign]

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

 

[joerd]

The only way I could get around this is by checking the whole sheet if your function (say it is called MyFunc) returns 0 anywhere, and then hiding the row. I tried to do it directly from the function itself, but then I cannot set any Range property. So, enter the infamous Worksheet_Calculate event (you know how to do event procedures ?

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.

 

[folk754]

Not possible. Functions won't execute any actions modifying the spreadsheets. The only way is with a subroutine, as shown by joerd below.

 

[savbill]

Not a function but...

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
---------------