Hide and unhide rows by selecting a letter from a userform ComboBox

[cbs81]

Hi, I have tried for ages .. but cant get it working..

i have a combobox and have A, B, C, as options in the combobox

then I have a table from row 10 to row 50

if A is selected from the combobox, I want excel to show only rows 10 to 20 and hide rows 20 to 50

if b is selected from the combobox, I want excel to show only rows 20 to 30 and hide rows 10 to 20 & 30 to 40

if c is selected from the combobox, I want excel to show only rows 30 to 40 and hide rows 10 to 30 & 40 to 50


in other words, i want all rows 10 to 50 hidden at all times, until a selection A, B, C is made in which i want thoes related rows to be shows and all other rows between rows 10 to 50 hidden...

Could someone please help me

thankyou

[mdmackillop]

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "A"
Rows("10:50").Hidden = True
Rows("10:20").Hidden = False
Case "B"
Rows("10:50").Hidden = True
Rows("20:30").Hidden = False
Case "C"
Rows("10:50").Hidden = True
Rows("30:40").Hidden = False
End Select
End Sub

[malik641]

Drat...Malcolm beat me to it...

Well, here was my example:
Const OptionA As String = "20:50"
Const OptionB As String = "10:19, 30:50"
Const OptionC As String = "10:29, 40:50"
Const AllRows As String = "10:50"

Public Sub HideRows(ByVal sh As Worksheet, ByVal OptChoice As String)
Dim strRowHide As String

' First unhide rows 10:50
sh.Range(AllRows).EntireRow.Hidden = False

' Find out which choice was selected
Select Case Strings.Right(OptChoice, 1)
Case "A": strRowHide = OptionA
Case "B": strRowHide = OptionB
Case "C": strRowHide = OptionC
' Check if OptChoice is blank "" or says "Show All", and if so, then exit the sub
' because we just want to show all.
Case Else: Exit Sub
End Select

' Now hide the rows passed to the procedure
sh.Range(strRowHide).EntireRow.Hidden = True
End Sub
This would be called from the userform like so:
Private Sub Userform_Initialize()
' Fill the combobox with options
With Me.ComboBox1
.AddItem "Option A"
.AddItem "Option B"
.AddItem "Option C"
.AddItem "Show All"
End With

End Sub

Private Sub CommandButton1_Click()
Call HideRows(ActiveSheet, Me.ComboBox1.Value)
Unload Me
End Sub
Also attached an example.

[cbs81]

Excellent..... that works..
now taking this a teeny step further...
This only works on the sheet that has the combobox in (say sheet1)... which is great
I would also like rows 10:50 to be hidden on sheet2 by default, BUT if I say select A from this combobox on sheet1, It will unhide rows 10:19 on sheet2..
if I say select B from this combobox on sheet1, It will unhide rows 20:35 on sheet2..
if I say select C from this combobox on sheet1, It will unhide rows 35:49 on sheet2..

how do i make this code hide/unhide rows on another sheet automatically ??
thankyou heaps

[malik641]

You would explicitly call out the other sheet. If you look at mdmackillop's code, particularly:

Rows("10:50").Hidden = True
This is equivalent to:

ActiveSheet.Rows("10:50").Hidden = True
' OR
Sheets("Sheet1").Rows("10:50").Hidden = True
' OR
Worksheets("Sheet1").Rows("10:50").Hidden = True And I say Sheet1 because you said the combobox is on Sheet1. So Add some more code to adjust Sheet2's rows to be hidden or now. I'd give you the code, but you would learn more this way :)

[cbs81]

Hi mate, This combo box is on sheet1. When A is selected, I want certain rows in sheet1 hidden and certain rows in sheet2 hidden... will this do the job??

Private Sub ComboBox1_Change()
Rows("10:50").Hidden = True
sheets("sheet2").rows("10:50").Hidden = True

Select Case ComboBox1.Value
Case "A"
Rows("10:19").Hidden = False
sheets("sheet2").rows("10:19").Hidden = False
Case "B"
Rows("20:29").Hidden = False
sheets("sheet2").rows("20:29").Hidden = False
Case "C"
Rows("30:39").Hidden = False
sheets("sheet2").rows("30:39").Hidden = False

End Select
End Sub

[malik641]

Close :). It looks like your showing the wrong rows on Sheet2...based on your other post, I mean:

I would also like rows 10:50 to be hidden on sheet2 by default, BUT if I say select A from this combobox on sheet1, It will unhide rows 10:19 on sheet2..
if I say select B from this combobox on sheet1, It will unhide rows 20:35 on sheet2..
if I say select C from this combobox on sheet1, It will unhide rows 35:49 on sheet2..
So here's what I see:

' Rows visible
Option Sheet1 Sheet2

OptA 10:19 10:19
OptB 20:29 20:35
OptC 30:39 35:49
Default *NOT* 10:50 *NOT* 10:50 Just check that against your code, unless you meant what you wrote in your new code ;) Otherwise, looks good to me :thumb Test it out!

[cbs81]

Hi, thankyou...

When A is selected from the combobox in sheet1 I want to:
Hide all rows in sheet1 from 10:50 EXCEPT for rows 10:20
AS WELL AS
Hide all rows in sheet2 from 100:160 EXCEPT for rows 100:110

When B is selected from the combobox in sheet1 I want to:
Hide all rows in sheet1 from 10:50 EXCEPT for rows 20:29
AS WELL AS
Hide all rows in sheet2 from 100:160 EXCEPT for rows 110:120

When C is selected from the combobox in sheet1 I want to:
Hide all rows in sheet1 from 10:50 EXCEPT for rows 30:39
AS WELL AS
Hide all rows in sheet2 from 100:160 EXCEPT for rows 120:129

etc... this is the actual real example of hiding different rows from a drop down selection on a particular sheet.... sorry for my example earlier.. I wasnt clear... it is in actual fact hiding different rows in these sheets..
thankyou and I hope someone can shed some light..

[malik641]

Glad to help out :)

Ok, check it out.

The way I would do this is to have a seperate procedure that would hide and show the rows you want to on the sheet you want it to be done with. For example:

' PUT THIS CODE IN A STANDARD MODULE ******************
Public Sub Hide(ByVal sh As Excel.Worksheet, _
ByVal HideRows As String, _
ByVal ShowRows As String)
' To use this, just pass the worksheet you want
' to hide rows on and a string that says which
' rows you want hidden, and which rows not.

' Start with hiding all the rows passed
sh.Rows(HideRows).Hidden = True

' Now Show the rows you want
sh.Rows(ShowRows).Hidden = False
End Sub
This procedure asks that you pass the worksheet you want it to work with, the rows to hide (e.g. "10:50") and the rows to show (e.g. "10:20"). I say to use this because if you add more options it is much easier to call the procedure with the parameters rather than hard write more "Rows("10:20").Hidden = False" many times over.

Based on your previous code (and the new info), I filled Option A's part for you to show you how to use the code. I'll leave it to you to fill out the rest ;)

Here's an example:
Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case "A"
' Sheet1 call to the procedure
Call Hide(Worksheets("Sheet1"), "10:50", "10:20")
' Sheet2 call to the procedure
Call Hide(Worksheets("Sheet2"), "100:160", "100:110")
Case "B"
'...Call Hide()
Case "C"
'...Call Hide()
Case Else
' Call the procedure with default values
End Select
End Sub
Hope this will get you moving!! I'll check this in the morning, have a good night for now! (Well, good afternoon I should say!)