refresh unhide row when chosing from a dropdown list.

**eight8** · 03-31-2007, 02:29 AM

Hi all,
i got this problem with my excel spreadsheet. I wanted to hide different row when i select different option from my dropdown list. At start, my B3 cell is empty therefore every row is shown. Thing work fine when i select any of the option(a,b,c,d). However when i changed my option from "a" to "b", row1 is still being hide. What can i do to refresh it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B3") = "a" Then
    Sheets("Line").Rows("1").EntireRow.Hidden = True
ElseIf Range("B3") = "b" Then
    Sheets("Line").Rows("2").EntireRow.Hidden = True
ElseIf Range("B3") = "c" Then
    Sheets("Line").Rows("3").EntireRow.Hidden = True
ElseIf Range("B3") = "d" Then
    Sheets("Line").Rows("4").EntireRow.Hidden = True
ElseIf Range("B3") = "e" Then
    Sheets("Line").Rows("5").EntireRow.Hidden = True
Else
    Sheets("Line").Rows("1:5").EntireRow.Hidden = False
End If
End Sub

**mikerickson** · 03-31-2007, 02:38 AM

You need to unhide the un-indicated rows.
inserting

ActiveSheet.Rows.Hidden = False

at the start of your routine will fix things. You might also consider inserting Application.ScreenUpdating=False/True to keep the flicker down.

By the way, why the selection_change event. Wouldn't the change event be called less often.

**VBA Noob** · 03-31-2007, 02:42 AM

When you enter C in B3 your B3 will then be hidden. May what to select a different entry cell

VBA Noob

**eight8** · 03-31-2007, 02:50 AM

Originally Posted by VBA Noob

When you enter C in B3 your B3 will then be hidden. May what to select a different entry cell

VBA Noob

sorry for the mistake. let say my dropdown list cell is not within the row which i want to hide/unhide?

**mikerickson** · 03-31-2007, 02:52 AM

If you have change from "a" to "b" the routine is not getting to the Else-Unhide. You have to unhide before re-hiding.

**VBA Noob** · 03-31-2007, 02:54 AM

Here's an alternative. Uses the case statement.

Also allows upper, lower or proper case

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
ActiveSheet.Rows.Hidden = False
Application.ScreenUpdating = False
For Each c In Range("B3")
Select Case LCase(c)
Case LCase("a")
    Sheets("Line").Rows("1").EntireRow.Hidden = True
Case LCase("b")
    Sheets("Line").Rows("2").EntireRow.Hidden = True
Case LCase("c")
Sheets("Line").Rows("3").EntireRow.Hidden = True
Case LCase("d")
    Sheets("Line").Rows("4").EntireRow.Hidden = True
Case LCase("e")
    Sheets("Line").Rows("5").EntireRow.Hidden = True
Case Else
Sheets("Line").Rows("1:5").EntireRow.Hidden = False
End Select
Next c
Application.ScreenUpdating = True
End Sub

VBA Noob

**mikerickson** · 03-31-2007, 03:07 AM

This is in the Change event. It will change things without having to select a new range.Like Noob's routine, it will work on CAPs

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
If Application.Intersect(Range("b3"), Target) Is Nothing Then Exit Sub
Range("a1:a5").EntireRow.Hidden = False
On Error Resume Next
i = Asc(Range("b3")) - 96
On Error Goto 0
If i < 1 Or 5 < i Then Exit Sub
Application.ScreenUpdating = False

Cells(i, 1).EntireRow.Hidden = True
Target.Select
Application.ScreenUpdating = True
End Sub

**eight8** · 03-31-2007, 02:53 AM

Originally Posted by mikerickson

You need to unhide the un-indicated rows.
inserting

ActiveSheet.Rows.Hidden = False

at the start of your routine will fix things. You might also consider inserting Application.ScreenUpdating=False/True to keep the flicker down.

By the way, why the selection_change event. Wouldn't the change event be called less often.

Mind telling me where to look for more information regarding Applicating.ScreenUpdating = False/True?

Sorry,very new to excel programming. I am working thru this problem by problem to achieve my required work. The selection should not be change once only but i am scared that user might select the wrong one at first and related rows are being hide when they change from the initial selection to the next selection.

refresh unhide row when chosing from a dropdown list.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

refresh unhide row when chosing from a dropdown list.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions