hide combo box

[JD]

Based on selections that a user makes at the beginning of my Excel file, I
would like certain combo boxes (form control) to be seen and other combo
boxes to be hidden. Is there a way to hide combo boxes (form control)?

I know it would be easier to just use a list in data validation, but I am
trying to make this as user friendly as possible. Meaning, I want the arrows
to be seen in the drop down at all times, not just when the cell is active
(which is the case with data validation - unless there is something I am
missing there).

Thank you!

 

[Rick Rothstein]

When you say "form control"... do you mean a control from the Forms (note
the 's') toolbar (and, hence, located on the worksheet) or do you mean a
control located on a display UserForm?

Also, describe "based on selections" for us in more detail. Do you mean cell
selection, changing values in cells, selections made from the combo boxes
(if more than one, which ones), something else? Remember, we cannot see your
worksheet(s) nor do we know what you want to do on it... so you must
describe your set up and intentions in enough detail so we can imagine what
your set up looks like and understand what you want to do with it.

 

[JD]

I believe from the Forms Toolbar. I am using Excel 2007, in the Developer
ribbon -> Insert (Here I have two categories to choose from "Form Controls"
and "ActiveX Controls") -> I inserted a combo box from the "Form Controls"
category. Is that more helpful?

My worksheet is an enrollment form. It allows the user who is filling out
the enrollment form to select that they are either representing an individual
school or a district that includes more than one school. If the user is
representing an individual school, then I want them to see only one combo box
where they will choose if they are a school that is one of three options -
Pre-K to K, Grades 1-8, or Grades 9-12. However, if the representative
filling in the form is filling it out for a district, then I allow them space
to fill in information on 10 different schools. So, in this case I would
need a combo box for each of the 10 schools.

Another way to think about it that may be helpful is that in my worksheet,
rows 45 - 80 include all of the cells I want filled in if it is an individual
school, and in these rows I have placed my combo box for this one school.
Rows 81 - 200 include all of the cells that need to be filled in for the 10
schools that are within the school district, and in these rows I have placed
10 different combo boxes, one for each school.

So, if the user filling in my enrollment form selects that they are
representing an individual school, then I want rows 45-80 to be seen
(including the one combo box) and I want rows 81-200 to be hidden (the 10
combo boxes for the different schools should be hidden as well). And vice
versa - if the user selects that they represent a school district, then I
want rows 45-80 to be hidden and rows 81-200 to be seen.


I have two option buttons (I inserted these from the same location under
Form Controls that I inserted the combo boxes). I have assigned a macro to
these buttons that hides the appropriate rows based on the selection made
(individual school or district)

I hope that I have better described my situation now. If there is more that
I need to explain let me know. Thank you for your help!

 

[Rick Rothstein]

Okay, you will need to add code like the following to the macros you
assigned to your Option Buttons.

Add to Option Button 1's macro
===============================
Dim X As Long
With Worksheets("Sheet1")
For X = 4 To 12
If .Shapes("Option Button 1").ControlFormat.Value = 1 Then
.Shapes("Drop Down " & X).Visible = msoFalse
End If
Next
End With
End Sub

Add to Option Button 2's macro
===============================
Dim X As Long
With Worksheets("Sheet1")
For X = 4 To 12
If .Shapes("Option Button 2").ControlFormat.Value = 1 Then
.Shapes("Drop Down " & X).Visible = msoTrue
End If
Next
End With
End Sub

Note I have assumed your option buttons are named "Option Button 1" for the
first and "Option Button 2" for the other. These names appear in the If
statements, so if your names are different, you will have to change them in
the code. Also, I have assumed your "Combo Boxes" (the Form's toolbar names
them "Drop Down #" where # is a sequentially assigned number) are
sequentially numbered from 3 to 12 (if not, you will have to handle them
individually rather than within a For..Next loop)... the loops hide or show
the last 9 of them (the first is always displayed). Also change the name
referenced in the With statements to the actual name of your worksheet.

 

[JD]

This makes sense. My only problem is that I can't find where to name or
label my combo boxes. When I right click on the combo box, there is not an
option to select Properties where I could change the name. The only option
relevant to changing the name is Format Control. Then there are five tabs -
Size, Protection, Properties, Web and Control. None of these locations
allows me to identify the name of the combo box. Any ideas how I can
identify or change the name of the combo box so that I will be able to
reference it in my macro code?

 

[Rick Rothstein]

Yeah, I know, control names from the Forms Toolbar are a real pain. You can
see all the control names on your worksheet by running this code line in the
Immediate window...

For X = 1 To 100 : ? Sheet1.Shapes(X).Name : Next

Change the Sheet1 reference to your actual sheet name. The code line will
error out when it runs out of controls (unless you have more than 100
controls on the worksheet). Another way to identify individual control names
is to right click on them, select Assign Macro from the popup list, and note
the control name part of the suggested name the the dialog box offers. Once
you have identified a particular control's name, you can change it from the
Immediate window like this...

Sheet1.Shapes("Drop Down 15").Name = "NewName"

I don't tend to work with Forms Toolbar controls too much myself, so there
may be a more direct way to do the above; but, if there is, I am not aware
of it.

 

[JD]

Thanks a lot! I appreciate your help, this worked great! I agree it was a
pain to change the names, but I like the shape and design of the combo boxes
from the Forms Toolbar more than I like them from the ActiveX Control Toolbar.

Thanks again!