Hide Combo Boxes with macros

[juancarlos]

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

 

[Jacob Skaria]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

Oops.

It's Jacob, not Jakob.

Sorry Jacob.

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]

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

Oops.

It's Jacob, not Jakob.

Sorry Jacob.

 

[Dave Peterson]

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

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

Oops.

It's Jacob, not Jakob.

Sorry Jacob.

 

[juancarlos]

Dave,
the changes worked...thank for your help.

--
Juan Carlos

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

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