Prev: Changing Name
Next: duplicating data
From: Johanna Gronlund on 1 Apr 2010 10:36
Hello,

I have a problem that I am unable to solve. I wondered if there is anyone
who could think of a solution.

I have two comboboxes (ComboBox1 and ComboBox2). In the first combobox the
user can select whether they want to view rows 37-127 or rows 128-207. The
code currently looks like this:

Sub ComboBox1_Change()
' Combobox Value Is changed
varData = Range("P21").Value2
' unhide ranges
On Error GoTo 100
Application.ScreenUpdating = False
Sheet3.Range("A37:A127").EntireRow.Hidden = False
Sheet3.Range("A128:A207").EntireRow.Hidden = True
Sheet3.Range("A102, A183, A107:A112, A188:A193").EntireRow.Hidden =
True

Select Case varData
Case 2
Sheet3.Range("A128:A207").EntireRow.Hidden = False
Sheet3.Range("A37:A127").EntireRow.Hidden = True
Sheet3.Range("A102, A183, A107:A112, A188:A193").EntireRow.Hidden =
True

End Select
100:
Application.ScreenUpdating = True
End Sub

In the 2nd combobox the user can select whether they want to enter their
values on rows:
- A51:A52 (if the first option is selected in ComboBox1) or A145 (if the
second option is selected in ComboBox1)
OR
- A43:A50 (if the first option is selected in ComboBox1) or A137:A144 (if
the second option is selected in ComboBox1)

So, there are four different combinations of hidden/unhidden rows. My
current solution does not work because when the user makes their 2nd
selection, it unhides previously unhidden rows, eg either A51:A52, A145,
A43:A50 or A137:A144.

Is there a way to have an if statement in the macro(s) that does the
following:
- P21=1 and A33=1, rows A128:A207 AND A51:A52 are hidden
- P21=1 and A33=2, rows A128:A207 AND A43:A50 are hidden
- P21=2 and A33=1, rows A37:A127 AND A145 are hidden
- P21=2 and A33=1, rows A37:A127 AND A137:A144 are hidden
- rows A102, A183 A107:A112 and A188:A193 should always be hidden
- all other rows should be unhidden, including the previously hidden selection

Also, currently I have two macros attached to the two dropdown menus that
update cells P21 and A33. If the if statements can combine the two macros,
where would I attach the macro? Not on the combobox I assume. Would I need an
additional button?

I would very much appreciate your help!

Johanna
From: B Lynn B on 1 Apr 2010 15:35
If this were my problem, I would have each of the two combo box's click or
change event record an associated value to a specified location on the
spreadsheet. Then I'd have a separate "hider decider" column (probably
hidden) with formulas to look at the two indicator spots. Each formula would
look at the combination of the two values and decide whether to return a 1 or
0. Then I would loop through the pertinent cells in that column to hide or
unhide the individual rows appropriately.

I've used that approach to handle similar tasks, and with some fairly
complex formulas to decide about different combinations of factors. Works
like a charm.

"Johanna Gronlund" wrote:

> Hello,
>
> I have a problem that I am unable to solve. I wondered if there is anyone
> who could think of a solution.
>
> I have two comboboxes (ComboBox1 and ComboBox2). In the first combobox the
> user can select whether they want to view rows 37-127 or rows 128-207. The
> code currently looks like this:
>
> Sub ComboBox1_Change()
> ' Combobox Value Is changed
> varData = Range("P21").Value2
> ' unhide ranges
> On Error GoTo 100
> Application.ScreenUpdating = False
> Sheet3.Range("A37:A127").EntireRow.Hidden = False
> Sheet3.Range("A128:A207").EntireRow.Hidden = True
> Sheet3.Range("A102, A183, A107:A112, A188:A193").EntireRow.Hidden =
> True
>
> Select Case varData
> Case 2
> Sheet3.Range("A128:A207").EntireRow.Hidden = False
> Sheet3.Range("A37:A127").EntireRow.Hidden = True
> Sheet3.Range("A102, A183, A107:A112, A188:A193").EntireRow.Hidden =
> True
>
> End Select
> 100:
> Application.ScreenUpdating = True
> End Sub
>
> In the 2nd combobox the user can select whether they want to enter their
> values on rows:
> - A51:A52 (if the first option is selected in ComboBox1) or A145 (if the
> second option is selected in ComboBox1)
> OR
> - A43:A50 (if the first option is selected in ComboBox1) or A137:A144 (if
> the second option is selected in ComboBox1)
>
> So, there are four different combinations of hidden/unhidden rows. My
> current solution does not work because when the user makes their 2nd
> selection, it unhides previously unhidden rows, eg either A51:A52, A145,
> A43:A50 or A137:A144.
>
> Is there a way to have an if statement in the macro(s) that does the
> following:
> - P21=1 and A33=1, rows A128:A207 AND A51:A52 are hidden
> - P21=1 and A33=2, rows A128:A207 AND A43:A50 are hidden
> - P21=2 and A33=1, rows A37:A127 AND A145 are hidden
> - P21=2 and A33=1, rows A37:A127 AND A137:A144 are hidden
> - rows A102, A183 A107:A112 and A188:A193 should always be hidden
> - all other rows should be unhidden, including the previously hidden selection
>
> Also, currently I have two macros attached to the two dropdown menus that
> update cells P21 and A33. If the if statements can combine the two macros,
> where would I attach the macro? Not on the combobox I assume. Would I need an
> additional button?
>
> I would very much appreciate your help!
>
> Johanna
 | 
Pages: 1
Prev: Changing Name
Next: duplicating data