Hide a combo box (form control), based on a cell value - Excel 2010

Hi guys

I'm TOTALLY new to this.

I am using Excel 2010

I have a worksheet called "Price Calculator", In it are 4 form control combo boxes:

1) "Drop Down 3", linked to cell $A$11
2) "Drop Down 11"
3) "Drop Down 12"
4) "Drop Down 13"

(I've got these names from the section to the left of the formula bar when the respective combo box is right clicked)

What I'm trying to achieve is:

whenever the cell link value to "Drop Down 3" <2 (i.e. when $A$11 <2) , I want the other 3 combo boxes to be automatically hidden
and whenever $A$11 >= 2, for them to reappear

Could someone please help me with the VBA code? And where do I put this code exactly? (in case I've been doing that bit wrong)

I have tried everything I can find on the internet, but nothing seems to trigger a change event

Would really appreciate your help )

Hi and thanks for your help.

The Combo Box Names are exactly as they appear in the section to the left of the formula bar when the respective combo box is right clicked

I tried the code that you offered by right clicking the sheet tab and "view code" and pasting it onto the white coding area of the correct worksheet, and it returns an error "object required".

Any ideas?

Originally Posted by p24leclerc

this macro at the sheet level could help you.

Please Login or Register  to view this content.

It is pretty simple, each time your selection change in that sheet, this macro is run and it check the value of cell A11 and hide or unhide the comboboxes.

Now check carefully the names of your comboboxes as the one you gave are not good ones. You can't put space in a combobox name.
With some minor adjustments, you should be able to get this macro work for you.
Good luck.

THIS IS NOW SOLVED

Your code above returns an error, and when it doesn't, it simply does not trigger an event.

The names I offered of the Combo Boxes are totally exactly right

The problem is when a link to a combo box changes value, this does NOT trigger a change or selection change event.

The following works:

Private Sub Worksheet_Calculate()
Dim ws As Worksheet

Set ws = Sheets("Price Calculator")

If Range("A11") < 2 Then
ws.Shapes("Drop Down 11").Visible = False
ws.Shapes("Drop Down 12").Visible = False
ws.Shapes("Drop Down 13").Visible = False

Else
ws.Shapes("Drop Down 11").Visible = True
ws.Shapes("Drop Down 12").Visible = True
ws.Shapes("Drop Down 13").Visible = True

End If

End Sub


Thanks again for your help, it was the start of my journey!

Link to cross post