|
Hide Combo Boxes with macros
I"m using belog data to hide some rows. What can I add to information below
to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos |
Hide Combo Boxes with macros
Try the below
Sub HideRowsBrazil() For Each Ctrl In ActiveSheet.OLEObjects Ctrl.Placement = 1 Next Ctrl If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "juancarlos" wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos |
Hide Combo Boxes with macros
Just to add to Jakob's response...
If you're using comboboxes from the control toolbox toolbar, you can rightclick on each of them (while in design mode), choose Format Control. Then on the Properties tab, you can choose "Move and Size with cells". Then if your comboboxes are within the range being hidden, they'll hide, too. Jakob's code included this line: Ctrl.Placement = 1 This 1 is the equivalent of teh "move and size with cells". He could have used Excel's VBA constant: xlMoveAndSize, too. But his code does try change this setting for each control from the control toolbox toolbar. You may not want all of them touched. juancarlos wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos -- Dave Peterson |
Hide Combo Boxes with macros
Oops.
It's Jacob, not Jakob. Sorry Jacob. Dave Peterson wrote: Just to add to Jakob's response... If you're using comboboxes from the control toolbox toolbar, you can rightclick on each of them (while in design mode), choose Format Control. Then on the Properties tab, you can choose "Move and Size with cells". Then if your comboboxes are within the range being hidden, they'll hide, too. Jakob's code included this line: Ctrl.Placement = 1 This 1 is the equivalent of teh "move and size with cells". He could have used Excel's VBA constant: xlMoveAndSize, too. But his code does try change this setting for each control from the control toolbox toolbar. You may not want all of them touched. juancarlos wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos -- Dave Peterson -- Dave Peterson |
Hide Combo Boxes with macros
Thanks for the help...but the combo boxes are still showing.
I'm using the combo boxes from Toolbox - forms I'm not really sure how to use the Control Toolbox - Combo boxes -- Juan Carlos "Dave Peterson" wrote: Oops. It's Jacob, not Jakob. Sorry Jacob. Dave Peterson wrote: Just to add to Jakob's response... If you're using comboboxes from the control toolbox toolbar, you can rightclick on each of them (while in design mode), choose Format Control. Then on the Properties tab, you can choose "Move and Size with cells". Then if your comboboxes are within the range being hidden, they'll hide, too. Jakob's code included this line: Ctrl.Placement = 1 This 1 is the equivalent of teh "move and size with cells". He could have used Excel's VBA constant: xlMoveAndSize, too. But his code does try change this setting for each control from the control toolbox toolbar. You may not want all of them touched. juancarlos wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos -- Dave Peterson -- Dave Peterson |
Hide Combo Boxes with macros
The comboboxes from the control toolbox work pretty much the same way that the
dropdowns from the Forms toolbar work. But the way you change the settings is different. If you rightclick on a dropdown from the Forms toolbar, you'll be able to choose format|control and on the Control tab, you can assign/change the input range and cell link. But the cell link returns an index into that list (1 through the number of items in your list). You need another cell with a formula to retrieve the value chosen in the dropdown (or some code). If you go into design mode (another icon on the Control Toolbox toolbar), you can right click on each and choose properties. In the properties window, you can scroll down to the ListFillRange and type in the equivalent of the "input range". And in the LinkedCell, you can type in the address for the equivalent of the "cell link". And the value of that linked cell is the value that you see in the combobox from the control toolbox toolbar. There are lots of additional properties that you can use to make it look pretty, too. But if you rightclick on the dropdown from the Forms toolbar and choose Format Control, you'll notice that it only has 4 tabs (the Control tab isn't there). But on the Properties tab of that dialog, you'll see that there is an option to move and size with cells. The dropdown from the Forms toolbar doesn't have that option (it's actually grayed out) available. So your choices are to replace all the dropdowns from the Forms toolbar with comboboxes from the Control toolbox toolbar. Or to hide the dropdown in code. Option Explicit Sub HideRowsBrazil() Dim myDD As DropDown Dim DDVisible As Boolean With ActiveSheet If .Rows(141).RowHeight = 0 Then .Rows("141:161").Hidden = False DDVisible = True Else .Rows("141:161").Hidden = True DDVisible = False End If For Each myDD In .DropDowns If myDD.TopLeftCell.Row = 141 _ And myDD.TopLeftCell.Row <= 161 Then myDD.Visible = DDVisible End If Next myDD End With End Sub This actually checks to see where the topleftcell of the dropdown is and then hides/shows it accordingly. =========== There is another option that is by far the easiest if you can use it. That's Data|Validation. Since it's a plain old cell, it'll hide when you hide the row. If you've never used data|validation, start at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html juancarlos wrote: Thanks for the help...but the combo boxes are still showing. I'm using the combo boxes from Toolbox - forms I'm not really sure how to use the Control Toolbox - Combo boxes -- Juan Carlos "Dave Peterson" wrote: Oops. It's Jacob, not Jakob. Sorry Jacob. Dave Peterson wrote: Just to add to Jakob's response... If you're using comboboxes from the control toolbox toolbar, you can rightclick on each of them (while in design mode), choose Format Control. Then on the Properties tab, you can choose "Move and Size with cells". Then if your comboboxes are within the range being hidden, they'll hide, too. Jakob's code included this line: Ctrl.Placement = 1 This 1 is the equivalent of teh "move and size with cells". He could have used Excel's VBA constant: xlMoveAndSize, too. But his code does try change this setting for each control from the control toolbox toolbar. You may not want all of them touched. juancarlos wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Hide Combo Boxes with macros
Dave,
the changes worked...thank for your help. -- Juan Carlos "Dave Peterson" wrote: The comboboxes from the control toolbox work pretty much the same way that the dropdowns from the Forms toolbar work. But the way you change the settings is different. If you rightclick on a dropdown from the Forms toolbar, you'll be able to choose format|control and on the Control tab, you can assign/change the input range and cell link. But the cell link returns an index into that list (1 through the number of items in your list). You need another cell with a formula to retrieve the value chosen in the dropdown (or some code). If you go into design mode (another icon on the Control Toolbox toolbar), you can right click on each and choose properties. In the properties window, you can scroll down to the ListFillRange and type in the equivalent of the "input range". And in the LinkedCell, you can type in the address for the equivalent of the "cell link". And the value of that linked cell is the value that you see in the combobox from the control toolbox toolbar. There are lots of additional properties that you can use to make it look pretty, too. But if you rightclick on the dropdown from the Forms toolbar and choose Format Control, you'll notice that it only has 4 tabs (the Control tab isn't there). But on the Properties tab of that dialog, you'll see that there is an option to move and size with cells. The dropdown from the Forms toolbar doesn't have that option (it's actually grayed out) available. So your choices are to replace all the dropdowns from the Forms toolbar with comboboxes from the Control toolbox toolbar. Or to hide the dropdown in code. Option Explicit Sub HideRowsBrazil() Dim myDD As DropDown Dim DDVisible As Boolean With ActiveSheet If .Rows(141).RowHeight = 0 Then .Rows("141:161").Hidden = False DDVisible = True Else .Rows("141:161").Hidden = True DDVisible = False End If For Each myDD In .DropDowns If myDD.TopLeftCell.Row = 141 _ And myDD.TopLeftCell.Row <= 161 Then myDD.Visible = DDVisible End If Next myDD End With End Sub This actually checks to see where the topleftcell of the dropdown is and then hides/shows it accordingly. =========== There is another option that is by far the easiest if you can use it. That's Data|Validation. Since it's a plain old cell, it'll hide when you hide the row. If you've never used data|validation, start at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html juancarlos wrote: Thanks for the help...but the combo boxes are still showing. I'm using the combo boxes from Toolbox - forms I'm not really sure how to use the Control Toolbox - Combo boxes -- Juan Carlos "Dave Peterson" wrote: Oops. It's Jacob, not Jakob. Sorry Jacob. Dave Peterson wrote: Just to add to Jakob's response... If you're using comboboxes from the control toolbox toolbar, you can rightclick on each of them (while in design mode), choose Format Control. Then on the Properties tab, you can choose "Move and Size with cells". Then if your comboboxes are within the range being hidden, they'll hide, too. Jakob's code included this line: Ctrl.Placement = 1 This 1 is the equivalent of teh "move and size with cells". He could have used Excel's VBA constant: xlMoveAndSize, too. But his code does try change this setting for each control from the control toolbox toolbar. You may not want all of them touched. juancarlos wrote: I"m using belog data to hide some rows. What can I add to information below to hide the combo boxes within this rows? Sub HideRowsBrazil() If Rows("141").RowHeight = 0 Then Rows("141:161").Hidden = False Else Rows("141:161").Hidden = True End If End Sub -- Juan Carlos -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com