Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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. -- Johanna |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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. -- Ciao BlackSun |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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! -- Johanna "BlackSun" wrote: 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. -- Ciao BlackSun . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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 :-) -- Ciao BlackSun |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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...! -- Johanna "BlackSun" wrote: 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 :-) -- Ciao BlackSun . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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. -- Ciao BlackSun |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
when certain rows are hidden, can I also hide a combobox?
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. Johanna Gronlund wrote: 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...! -- Johanna "BlackSun" wrote: 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 :-) -- Ciao BlackSun . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do filters un-hide my hidden rows in Excel? | Excel Discussion (Misc queries) | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Hidden rows in worksheet also hide borders | Excel Discussion (Misc queries) | |||
Rows hidden by Autofilter vs hidden by changing the Hidden property | Excel Programming | |||
Hide or protect hidden rows/columns - macro help | Excel Programming |