My Rows won't hide...

[Taylor Francis]

This is my function...

Function GetNameForSummary(x, y)
If (Len(x) > 2) Then
Worksheets(1).Rows(y).Hidden = False
Else
Worksheets(1).Rows(y).Hidden = True
End If
GetNameForSummary = x
End Function


The function is called like this:
=GetNameForSummary(a1,row())

the desired function is if a1 is longer than 2character, show it in the
new cell (where the call is), if not, then hide the row that contains
this function call...

It won't hide the row...why?

Taylor

 

[Jim Rech]

Your User Defined Function, GetNameForSummary, is trying to effect a change
in the worksheet. All UDFs can do is return a result to the cell they are
in (just like Excel's worksheet functions). A UDF cannot "do things"
(print, save, hide rows, etc.)

 

[Taylor Francis]

How do you suggest I do this differntly, then...
T

 

[Jim Rech]

You have to find some other way to trigger the row hiding routine, other
than a call from a cell. You might try the Sheet Calculate event:

Private Sub Worksheet_Calculate()
If Len(Range("A1").Value) > 2 Then
Rows(4).Hidden = False
Else
Rows(4).Hidden = True
End If
End Sub

This would go in the sheet module for the worksheet in question (right-click
the worksheet tab and pick View Code).