Function macro

[sujittalukde]

I am using a UDF created by a macro. the same is using at a sheet. the code for function is :


Function area(x As Double, y As Double) As Double
If y = 0 Then
area = x * x
Else
area = x * y
End If
End Function


In sheet1, at cell A3, a dropdown list is made via data validation containing "Rectangle" and "Square". I want that if the user selects the "rectangle", it should show the Row3 & Row 4. If the user selects the "Square" it should hide the row4. I have made the macro to do the same in a module and tried to do the same by ws selection chage methd but then it refreshes the entire sheet. Can some one please help me in this matter?


Sub GetArea()
If ActiveSheet.Range("A3").Value = "Square" Then
Rows("4:4").Select
Selection.EntireRow.Hidden = True
Range("B4").Select
Selection.ClearContents
Range("B3").Select
End If
If ActiveSheet.Range("A3").Value = "Rectangle" Then
Rows("3:5").Select
Range("A5").Activate
Selection.EntireRow.Hidden = False
Range("B4").Select
End If
End Sub

I want that if the user selects the "rectangle", it should show the Row3 & Row 4. If the user selects the "Square" it should hide the row4.This should happen on changing the cell A3 automatically.

[Charlize]

What do you mean by 'refreshing entire sheet' ? Try this for square or rectanglePrivate Sub Worksheet_Change(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Row = 3 Then
Select Case Target.Value
Case "Square"
Target.EntireRow.Offset(1, 0).Hidden = True
Case "Rectangle"
Target.EntireRow.Offset(1, 0).Hidden = False
Case Else
Target.EntireRow.Offset(1, 0).Hidden = False
End Select
End If
End Sub

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If LCase(.Value) = "rectangle" Then
Me.Rows("3:4").Hidden = False
ElseIf LCase(.Value) = "square" Then
Me.Rows(4).Hidden = True
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub