Hide rows if drop down selected.
How do I hide selected rows in a worksheet if a control form list item is
selected? Should I be using a form control list instead? |
Hide rows if drop down selected.
I put a listbox from the Control toolbox toolbar on a worksheet.
I used the Worksheet_Activate event to populate that listbox (just for test data). Then I used code like this: Option Explicit Private Sub Worksheet_Activate() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 5 .AddItem "asdf" & iCtr Next iCtr End With End Sub Private Sub ListBox1_Change() If Me.ListBox1.ListIndex < 0 Then 'nothing selected Exit Sub End If 'show everything? Me.Rows.Hidden = False Select Case Me.ListBox1.ListIndex Case Is = 0 Me.Range("A10:a14").EntireRow.Hidden = True Case Is = 1 Me.Range("A13:a17").EntireRow.Hidden = True Case Else Beep End Select End Sub Greyson wrote: How do I hide selected rows in a worksheet if a control form list item is selected? Should I be using a form control list instead? -- Dave Peterson |
Hide rows if drop down selected.
Wow...sounds complicated for a seemingly simple task. I have the listbox on
the worksheet and I have that list populated with 11 items. 4 of the items (if selected) need to hide range a24:a31, 5 items need to hide range a18:a22, and 2 items hide range a18:a31. I have no idea what the code you wrote means - will it provide this flexibility if I paste it in the VB window? "Dave Peterson" wrote: I put a listbox from the Control toolbox toolbar on a worksheet. I used the Worksheet_Activate event to populate that listbox (just for test data). Then I used code like this: Option Explicit Private Sub Worksheet_Activate() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 5 .AddItem "asdf" & iCtr Next iCtr End With End Sub Private Sub ListBox1_Change() If Me.ListBox1.ListIndex < 0 Then 'nothing selected Exit Sub End If 'show everything? Me.Rows.Hidden = False Select Case Me.ListBox1.ListIndex Case Is = 0 Me.Range("A10:a14").EntireRow.Hidden = True Case Is = 1 Me.Range("A13:a17").EntireRow.Hidden = True Case Else Beep End Select End Sub Greyson wrote: How do I hide selected rows in a worksheet if a control form list item is selected? Should I be using a form control list instead? -- Dave Peterson |
Hide rows if drop down selected.
Chip Pearson has notes about worksheet events:
http://www.cpearson.com/excel/events.htm David McRitchie's notes: http://www.mvps.org/dmcritchie/excel/event.htm If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm And if you make changes the way you want, it may provide enough flexibility. Greyson wrote: Wow...sounds complicated for a seemingly simple task. I have the listbox on the worksheet and I have that list populated with 11 items. 4 of the items (if selected) need to hide range a24:a31, 5 items need to hide range a18:a22, and 2 items hide range a18:a31. I have no idea what the code you wrote means - will it provide this flexibility if I paste it in the VB window? "Dave Peterson" wrote: I put a listbox from the Control toolbox toolbar on a worksheet. I used the Worksheet_Activate event to populate that listbox (just for test data). Then I used code like this: Option Explicit Private Sub Worksheet_Activate() Dim iCtr As Long With Me.ListBox1 For iCtr = 1 To 5 .AddItem "asdf" & iCtr Next iCtr End With End Sub Private Sub ListBox1_Change() If Me.ListBox1.ListIndex < 0 Then 'nothing selected Exit Sub End If 'show everything? Me.Rows.Hidden = False Select Case Me.ListBox1.ListIndex Case Is = 0 Me.Range("A10:a14").EntireRow.Hidden = True Case Is = 1 Me.Range("A13:a17").EntireRow.Hidden = True Case Else Beep End Select End Sub Greyson wrote: How do I hide selected rows in a worksheet if a control form list item is selected? Should I be using a form control list instead? -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com