×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Trick a function to perform an action?

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

RE: Trick a function to perform an action?

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.

RE: Trick a function to perform an action?

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

RE: Trick a function to perform an action?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources