Row Hiding doesn't Seems to Work
 

I did a macro inside a module to be called in the formula bar.
Basically, I have a drop down list of models in a cell (A1) that user can
select, which will show the detailed desciption of the selected model in the
next row (A2). I included an item of "Non-Standard model", so that if user
choose that item, I could hide the standard model's row (A2) and show the
non-standard one (A3). My function is as follows:

Function LookupCorrespond(ByVal Source As String, ByVal LookupList As Range,
ByRef StandardRow As Long, ByRef NonStandardRow As Long) As String

Model = Application.WorksheetFunction.VLookup(Source, LookupList, 2,
False)
If (Application.WorksheetFunction.IsError(Model)) Then
LookupCorrespond = ""
Else
Application.ScreenUpdating = False
If (StrComp(Model, "Non-Standard", vbStringCompare) Eqv 0) Then
Rows(StandardRow & ":" & StandardRow).Hidden = True
Rows(NonStandardRow & ":" & NonStandardRow).Hidden = False
Else
Rows(StandardRow & ":" & StandardRow).Hidden = False
Rows(NonStandardRow & ":" & NonStandardRow).Hidden = True
End If
Application.ScreenUpdating = True
LookupCorrespond = CStr(Model)
End If
End Function

The description showing part works beautifully, but it doesn't seems to be
able to hide & unhide the desired rows at all. ANyone can point out anything
wrong?
Thanks!
________
cLiffordiL

Row Hiding doesn't Seems to Work
 

Hi,

you cannot use function to hide rows, use sub instead.

regards,

Ivan

Row Hiding doesn't Seems to Work
 

I did some further stepping & debugging and it seems that the lines:

Rows(StandardRow & ":" & StandardRow).Hidden = True
Rows(NonStandardRow & ":" & NonStandardRow).Hidden = True

are not updating the boolean valuie. But the code works perfectly when I
re-implement it as a Sub & use a command button to run hte maco.
Suggestions?
Thanks!
________
cLiffordiL

"cLiffordiL" wrote in message
...
I did a macro inside a module to be called in the formula bar.
Basically, I have a drop down list of models in a cell (A1) that user can
select, which will show the detailed desciption of the selected model in
the next row (A2). I included an item of "Non-Standard model", so that if
user choose that item, I could hide the standard model's row (A2) and show
the non-standard one (A3). My function is as follows:

Function LookupCorrespond(ByVal Source As String, ByVal LookupList As
Range, ByRef StandardRow As Long, ByRef NonStandardRow As Long) As String

Model = Application.WorksheetFunction.VLookup(Source, LookupList, 2,
False)
If (Application.WorksheetFunction.IsError(Model)) Then
LookupCorrespond = ""
Else
Application.ScreenUpdating = False
If (StrComp(Model, "Non-Standard", vbStringCompare) Eqv 0) Then
Rows(StandardRow & ":" & StandardRow).Hidden = True
Rows(NonStandardRow & ":" & NonStandardRow).Hidden = False
Else
Rows(StandardRow & ":" & StandardRow).Hidden = False
Rows(NonStandardRow & ":" & NonStandardRow).Hidden = True
End If
Application.ScreenUpdating = True
LookupCorrespond = CStr(Model)
End If
End Function

The description showing part works beautifully, but it doesn't seems to
be able to hide & unhide the desired rows at all. ANyone can point out
anything wrong?
Thanks!
________
cLiffordiL