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

billybob12345

New Member
Joined
Dec 4, 2017
Messages
9
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,611
This is working for me.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$O$17" Then
Select Case UCase(Target)
Case Is = "YES": CheckBoxes("Check Box 1").Visible = True
Case Is = "NO": CheckBoxes("Check Box 1").Visible = False
End Select
End If
Application.EnableEvents = True
End Sub
 

billybob12345

New Member
Joined
Dec 4, 2017
Messages
9
Hi,

Thanks for the reply. It still does not work. However if I manually enter yes or no it works. It also works the first time a formula is put in - it detects the YES and displays the box. After that it does not seem to update when the formula changes.

Thanks
Peter
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,611
That's strange, i'm on Excel 2013 and it's disappearing and reappearing when the dropdown selection changes.

Do you have the macro inserted on the Sheet?

Sorry, confused off a similar post, checking the formula side of things..
 
Last edited:

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,611

ADVERTISEMENT

How's this?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

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


End Sub
 

billybob12345

New Member
Joined
Dec 4, 2017
Messages
9
Hi,

I right click on the sheet and view code I paste it in there.

I have tried it with worksheet change and general declarations - same result. Does not update based on formula but does manually. If the box is not visible and the formula is put in for the first time it becomes available but then will not change again no matter what the formula is but does work manually. Its like its not reading it again.

Thanks for your help
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,611

ADVERTISEMENT

The problem was that the target.address O17 is not the cell you are selecting, only when selected it will make the change(like when you enter the formula).

Try my above solution. When you target A1 and O17 changes as a result.
 
Last edited:

billybob12345

New Member
Joined
Dec 4, 2017
Messages
9
Hi,

Thanks for that - however its got a problem and i think i know what it is. The cell A1 with bread in it is the result of a formula and not static text. Such as =IF(B1="FLOUR","BREAD","")
I think the code is trying to look for the text word and that is where it falls down?

Thanks Again
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,611
You need Target.Address = $A$1 to be the cell you are clicking on that prompts the change. If you change that part of the code you should be in business.

What are you doing physically to make the yes/no change?
 
Last edited:

billybob12345

New Member
Joined
Dec 4, 2017
Messages
9
Hi,

Here is a picture and explanation. Ideally i had wanted the checkbox to be visible from a choice in the drop down box but i could not get that to work.

The drop down box has three items and the list is in cells E2:E4
The cell link is B4
Cell C4 looks to see if the value from the drop down is 1 by using the formula =IF(B4=1,"YES","NO")

The checkbox should appear or disappear based on whether it is a yes or a no.

Blah.png


Thank you for all of this time and effort.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top