Checkbox Visible if Cell Value.... - Manual entry Works - Formula Does not

[billybob12345]

Hi,

I was wondering if I might ask for some of your excellent support please. This forum has been a source of learning for me so thanks very much.

I am unable to locate an answer for my current dilemma - which is almost working lol.

I have a cell $O$17 that says =if(A1="bread","YES","NO") Which works fine.
I want to display a checkbox if YES is output.
My code works if I manually input YES or NO into cell $O$17 but does not work otherwise.

Status > Windows 7, Excel 2007, Form Checkbox not ActiveX, Inexperienced user of VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Target.Address = "$O$17" Then
Select Case UCase(Target)
Case Is = "YES": Shapes("CheckBox1").Visible = msoTrue
Case Is = "NO": Shapes("CheckBox1").Visible = msoFalse
End Select
End If
Application.EnableEvents = True
End Sub


Many Thanks
Billy

 

[mrshl9898]

Ahh, that makes things much different, and out of my current knowledge...

If you insert the dropdown in say B2, with a Data Validation dropdown (In the Data tab), then you could just use:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" And UCase(Range("C4").Value) = "YES" Then
    CheckBoxes("Check Box 1").Visible = True
    End If
    If Target.Address = "$B$2" And UCase(Range("C4").Value) = "NO" Then
    CheckBoxes("Check Box 1").Visible = False
    End If
    
End Sub

But i'm not sure how to interact with the object you are using... Apologies.

You would need something along these lines perhaps, but this does not work:

Private Sub Worksheet_Change(ByVal Target As Range)

If DropDown("Drop Down 2").Value = "Bread" Then
CheckBoxes("Check Box 3").Visible = True
End If
CheckBoxes("Check Box 3").Visible = False
End If


End Sub

 

[My Aswer Is This]

Sheet change events will not be activated due to a formula Change.
It must be a manual change.

Now what does "Milk" "Bread" "Oranges" have to do with this:

Why not just say If "B4" = "Bread" do this
If "B4" = "Oranges" do this:

Why does your original script say
"$O$17"

But you image shows "B4"

 

[billybob12345]

Hi,

Does that mean that the code to hide and show a checkbox can only be done if the input is manual because the cell it is looking at is manual entry. Is it possible to do this through formula or other inputs such as the drop down directly?

Thanks again for your support.

 

[My Aswer Is This]

Yes if you enter a value into a cell using a data validation cell that is considered a manual entry.
I assume when you say "drop down" you mean data validation list.

 

[My Aswer Is This]

You never answered my questions. Like why do you need a formula?And what are we using "Bread" for?

 

[billybob12345]

Hi,

It is a hypothetical situation. It doesnt have to be bread it could be anything. What is constant is that there is a drop down list. It has an input range (E2:E4) and cell link (B4)

As the user changes the option in the list it changes the value in B4.

If the value in (B4) is YES then I need the check box to appear and if not then disappear.

Any help would be appreciated. Been trying and trying for days

 

[billybob12345]

I was wondering - if it will not work using a combo box would it work with a list using data validation?

Thank You

 

[mrshl9898]

Check your inbox, sent you a link to a file with it working using data validation.

 

[billybob12345]

Hi,

Thank you very much - I hope Santa brings you tons of presents! Your a absolutely smashing!

Thanks

 

[mrshl9898]

As long as it's not Coal, again!

Glad you're sorted.