Conditionally Show/Hide ComboBox Based on Cell Value

[Mayanwolfe]

Hi, I'm quite new to VBA, but I am attempting to get a Forms ComboBox to appear or disappear based on whether a certain cell (P7) reads YES or NO. P7 in turn updates in turn based on a user-selected value. As of now, the ComboBox only appears or disppears if I go back in and out of the formula I entered into P7. Basically, I want my ComboBox to dynamically update based on the value in P7. That may sound a little muddled, so here is my code for the ComboBox:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("P7")) Is Nothing Then
         If UCase(Target) = "YES" Then
             Me.Shapes.Range("Drop Down 30").Visible = msoTrue
         Else
             Me.Shapes.Range("Drop Down 30").Visible = msoFalse
         End If
     End If
 
End Sub

Thanks in advance for your help.

 

[Mayanwolfe]

bump

 

[p45cal]

the Change event is not triggered by changes in formula results.
In your code,
If Not Intersect(Target, Range("P7")) Is Nothing Then
change the Range("P7") part to the cell(s) which the user changes.
Also change the:
If UCase(Target) = "YES" Then
to:
If Range("P7") = "YES" Then

 

[Mayanwolfe]

That fixed it wonderfully! Thank you so much.