when certain rows are hidden, can I also hide a combobox?

  • Thread starter Johanna Gronlund
  • Start date
J

Johanna Gronlund

Hello,

I have a code that hides certain rows in sheets 3 and 4. Is there a way to
make 'Drop Down 3' and 'Drop Down 4' hidden when rows as per Case 2 are
hidden? The code I have so far is below:

Sub ComboBox1_Change()
' Combobox Value Is changed
varData = Range("B27").Value2
' unhide ranges
On Error GoTo 100
Application.ScreenUpdating = False
Sheet3.Range("A58:A68").EntireRow.Hidden = False
Sheet4.Range("A150:A185").EntireRow.Hidden = False
Sheet3.Range("A35:A57, A26").EntireRow.Hidden = False
Sheet4.Range("A12:A148").EntireRow.Hidden = False

Select Case varData
Case 2
Sheet3.Range("A58:A68").EntireRow.Hidden = True
Sheet4.Range("A150:A185").EntireRow.Hidden = True
Case 3
Sheet3.Range("A35:A57, A26").EntireRow.Hidden = True
Sheet4.Range("A12:A148").EntireRow.Hidden = True
End Select
100:
Application.ScreenUpdating = True
End Sub

Many thanks in advance.
 
B

BlackSun

Il 08/02/2010 12.52, Johanna Gronlund ha scritto:
Hello,

I have a code that hides certain rows in sheets 3 and 4. Is there a way to
make 'Drop Down 3' and 'Drop Down 4' hidden when rows as per Case 2 are
hidden? The code I have so far is below:

Sub ComboBox1_Change()
' Combobox Value Is changed
varData = Range("B27").Value2
' unhide ranges
On Error GoTo 100
Application.ScreenUpdating = False
Sheet3.Range("A58:A68").EntireRow.Hidden = False
Sheet4.Range("A150:A185").EntireRow.Hidden = False
Sheet3.Range("A35:A57, A26").EntireRow.Hidden = False
Sheet4.Range("A12:A148").EntireRow.Hidden = False

Select Case varData
Case 2
Sheet3.Range("A58:A68").EntireRow.Hidden = True
Sheet4.Range("A150:A185").EntireRow.Hidden = True
Case 3
Sheet3.Range("A35:A57, A26").EntireRow.Hidden = True
Sheet4.Range("A12:A148").EntireRow.Hidden = True
End Select
100:
Application.ScreenUpdating = True
End Sub

Many thanks in advance.
Hi,
if I have understood what you need; in the case statement, where you
hide the rows, set combobox.visible=false.
 
J

Johanna Gronlund

Thanks, I think that is exactly what I need to do. However, I am not able to
refer to the right combobox. How do I do that? I tried various things
including
combobox(Drop Down 3).visible=false but they just stopped the whole code
from working.

Sorry for being so ignorant. I am a total beginner with VB.

Thanks!
 
B

BlackSun

Il 08/02/2010 14.05, Johanna Gronlund ha scritto:
Thanks, I think that is exactly what I need to do. However, I am not able to
refer to the right combobox. How do I do that? I tried various things
including
combobox(Drop Down 3).visible=false but they just stopped the whole code
from working.

Sorry for being so ignorant. I am a total beginner with VB.

Thanks!

Hi,
select the control and see the name in the property tab; then write the
name and add .visible = False.

Let me know if you resolve :)
 
J

Johanna Gronlund

Hi,

I checked the names and they are Drop Down 3 and Drop Down 4 but it still
doeesn't work. Do I need to use:
'Drop Down 3'.visible = False
"Drop Down 3".visible = False
(Drop Down 3).visible = False
Drop Down 3.visible = False
or something else?

Sorry for being such beginner...!
 
B

BlackSun

Il 08/02/2010 15.04, Johanna Gronlund ha scritto:
Hi,

I checked the names and they are Drop Down 3 and Drop Down 4 but it still
doeesn't work. Do I need to use:
'Drop Down 3'.visible = False
"Drop Down 3".visible = False
(Drop Down 3).visible = False
Drop Down 3.visible = False
or something else?

Sorry for being such beginner...!
Can you make a screenshot of your property tab with one combobox selected?
Thanks.
 
D

Dave Peterson

If the code is in the Worksheet module and you created the dropdowns from the
Forms toolbar:

me.dropdowns("drop down 3").visible = ....

The Me keyword refers to the object that owns the code--in this case, it's the
worksheet.

There's a difference in behavior and code for dropdowns from the Forms toolbar
and comboboxes from the control toolbox toolbar.

And from the procedure names, it looks like you may have used comboboxes and
dropdowns on the same worksheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top