View Single Post
 
Old 07-11-2015, 10:32 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

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.
Code:
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
Reply With Quote