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?

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

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

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