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 combo box 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, I want 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

The change of value in the linked cell does not fire the Worksheet_Change event.
You could use the Worksheet_SelectionChange event but the 3 drop downs would not hide and unhide when the drop down 3 changes A11 until the cursor gets moved into another cell. This could leave things in the wrong state for considerable time.
I'd use the Worksheet_Calculate event. This requires making another cell somewhere on the sheet =A11 to make sure something gets calculated the instant the value in A11 changes. The Calculate event fires every time something, anything, on the sheet gets calculated so there is the potential that having this event do things could affect Excels performance in which case you could have the =A11 cell on a separate sheet with nothing else and move the code over to that sheet.

Right click on the sheet tab your drop downs are on, select view code then paste this into the sheet module that appears.

Thank you NoSparks!! Very helpful

Correct me if I'm wrong, my understanding of what you've said is:


1) Start a new worksheet in the same workbook, say its called "Backend"

2) Put a formula in a cell, say C1, and that formula should be
= 'Price Calculator'!$A$11

3) Change your code where it says:
If Range("A11") < 2 Then
To If Range("C1") < 2 Then

4) Paste this code onto the "Beckend" worksheet, and NOT the "Price Calculator" worksheet

If I have understood you correctly, what would the code call the combo boxes??

Would they not have to be referenced to the "Price Calculator" worksheet??



PS I have tried your code exactly as it is straight into the worksheet coding, and it works a treat but would still like the above guidance if poss!!!!

Yes, that's what I said. The only change would be If Range("C1") < 2 Then
The drop downs stay qualified as they are.

Must admit though, I haven't tested it this way.
Let me know if that works for you.

NoSparks,

It actually works without creating the second worksheet, and I have tested it perhaps not rigorously, but extensively.

I have actually adapted your code (i have never coded before) to include other form control objects, as follows:



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
ws.Shapes("Spinner 29").Visible = False
Else
ws.Shapes("Drop Down 11").Visible = True
ws.Shapes("Drop Down 12").Visible = True
ws.Shapes("Drop Down 13").Visible = True
ws.Shapes("Spinner 29").Visible = True
End If

If Range("A11") < 3 Then
ws.Shapes("Drop Down 17").Visible = False
ws.Shapes("Drop Down 18").Visible = False
ws.Shapes("Drop Down 19").Visible = False
ws.Shapes("Spinner 31").Visible = False
Else
ws.Shapes("Drop Down 17").Visible = True
ws.Shapes("Drop Down 18").Visible = True
ws.Shapes("Drop Down 19").Visible = True
ws.Shapes("Spinner 31").Visible = True
End If

If Range("C9") = 0 Then
ws.Shapes("Button 51").Visible = False
ws.Shapes("Button 52").Visible = False
ws.Shapes("Drop Down 42").Visible = False
ws.Shapes("Spinner 41").Visible = False
Else
ws.Shapes("Button 51").Visible = True
ws.Shapes("Button 52").Visible = True
ws.Shapes("Drop Down 42").Visible = True
ws.Shapes("Spinner 41").Visible = True
End If

End Sub


This works a treat!!

What everyone else on internet forums got 'wrong' is they had it down as a 'change' event, not a 'calculation' event.

You are officially a scholar, gentleman, and a genius!!

With great thanks!!

TRANSLATION ---> I've screwed it up before.

Glad I could help.

Please mark this thread as solved
and put links in your cross posts to all your other cross posts, as stipulated in all the forums rules, so others searching for a solution to a similar challenge can learn from our experience.

You said:

"Please mark this thread as solved
and put links in your cross posts to all your other cross posts, as stipulated in all the forums rules, so others searching for a solution to a similar challenge can learn from our experience."

I have no idea how to do that lol, but I'm on the case...

Thanks again

I have now marked it 'Solved', but have no idea how to put links on my 'cross posts' to all other 'cross posts'.... I'm not sure I know what 'cross posts' are...

Have a read of this for an understanding.

Thanks again NoSparks, I have now read it and had posted a few hours before in another forum.. Noticed also you have already posted the link there, so thank you.

Here is the link to the cross post:
http://www.excelforum.com/excel-prog...el-2010-a.html