Excel VBA show/hide help

[Igawa29]

USING OFFICE 2010

So I have an issue when a user clicks on a checkbox it will hide or show certain rows. Within these rows are dropdown boxes and checkboxes that I have coded to show or hide. Every once in awhile I notice that all the dropdown boxes bunch up at the top of the row. Is there a way to keep these anchored in so that they do not move on the user selection?

Here is my cell forumula: =EMBED("Forms.HTML:Select.1","")

VB Code:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
[10:35].EntireRow.Hidden = False
HTMLSelect1.Visible = True
HTMLSelect2.Visible = True
HTMLSelect3.Visible = True
HTMLSelect4.Visible = True
HTMLSelect5.Visible = True
HTMLSelect6.Visible = True
HTMLSelect7.Visible = True
HTMLSelect8.Visible = True
HTMLSelect9.Visible = True
HTMLSelect10.Visible = True
HTMLSelect11.Visible = True
HTMLSelect12.Visible = True
HTMLSelect13.Visible = True
HTMLSelect14.Visible = True
HTMLSelect15.Visible = True
Else: [10:35].EntireRow.Hidden = True
HTMLSelect1.Visible = False
HTMLSelect2.Visible = False
HTMLSelect3.Visible = False
HTMLSelect4.Visible = False
HTMLSelect5.Visible = False
HTMLSelect6.Visible = False
HTMLSelect7.Visible = False
HTMLSelect8.Visible = False
HTMLSelect9.Visible = False
HTMLSelect10.Visible = False
HTMLSelect11.Visible = False
HTMLSelect12.Visible = False
HTMLSelect13.Visible = False
HTMLSelect14.Visible = False
HTMLSelect15.Visible = False
End If
End Sub

 

[SkipVought]

hi,

In Format Controls > Properties TAB, are 3 Object Positioning Option Buttons.

Choose either of the "Move..." options as appropriate to your application, so if rows or columns change height/width, your controls will adjust to the cell accordingly.

You can also, much more specifically, use a Control's TopLeftCell property to align the Control's Top, Left, Height & Width properties with the TopLeftCell's Top, Left, Height & Width properties if needed.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Igawa29]

Thanks, I will give this a try.

 

[Andrzejek]

On the side note,
I am sure there is a nice way of looping thru your controls/check boxes, but consider this:

Private Sub CheckBox1_Click()
Dim b As Boolean

b = CheckBox1

[10:35].EntireRow.Hidden = Not(b)

HTMLSelect1.Visible = b
HTMLSelect2.Visible = b
HTMLSelect3.Visible = b
HTMLSelect4.Visible = b
HTMLSelect5.Visible = b
HTMLSelect6.Visible = b
HTMLSelect7.Visible = b
HTMLSelect8.Visible = b
HTMLSelect9.Visible = b
HTMLSelect10.Visible = b
HTMLSelect11.Visible = b
HTMLSelect12.Visible = b
HTMLSelect13.Visible = b
HTMLSelect14.Visible = b
HTMLSelect15.Visible = b

End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

So I have an issue when a user clicks on a checkbox it will hide or show certain rows.

Would you describe where A control (that are moving around or bunching up) IS positioned on your sheet relative to THE row that it hides or shows? I'm having trouble envisioning this.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Igawa29]

So basically Row 4 and Row 5 have checkboxes, called Option 1 and Option 2

Rows 10 - 35 have different selections the user can make if they choose Option 1.. if Option 2 is unchecked then they don't see the choices for Option 1.

Rows 37 - 50 have different selections if the user selects Option 2.


I just wanted to make sure things stay hidden that are not relevant to the user, that is where I ran into the dropdown boxes all grouping together I think on Row 10.

 

[SkipVought]

Have you considered using the AutoFilter as the CONTROL to show/hide a range or rows? It does the job very well!

But if you still want an option button control feature to make it easier on your users, then use the results of the control selection to control the AutoFilter.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Igawa29]

Have you considered using the AutoFilter as the CONTROL to show/hide a range or rows? It does the job very well!

But if you still want an option button control feature to make it easier on your users, then use the results of the control selection to control the AutoFilter.

Thank you, I will look into that as well. I just never had the dropdowns all group up like that before.. it was quite the surprise.

 

[SkipVought]

Actually, I think that it is a maintenance nightmare to have 15 controls.

You only mentioned TWO of the fifteen. What about the others? I would think that there's gotta be a better way to control the rows that display on your sheet!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}