[SOLVED] making a checkbox appear and disappear based on a cell's value

yoramsk@gmail.com
Jan 23, 9:22 am show options

From: "yora...@gmail.com" <yora...@gmail.com> - Find messages by this
author
Date: Mon, 23 Jan 2006 15:22:13 -0000
Local: Mon, Jan 23 2006 9:22 am
Subject: making a checkbox appear and disappear based on a cell's value

Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

i know this might have been addressed before but i cannot get the
following code to work. basically i want a checkbox to appear if cell
A1 is = "abc" and disappear if it is anything else. this checkbox was
created from the controls toolbar. so i started off the checkbox set
to invisible and then have an if/then statement to make it visible.
what am i doing wrong? right now the checkbox will not reappear.
thanks in advance for any help.


Private Sub Worksheets_Calculate()
ActiveSheet.CheckBox15.Visible = False
If Worksheets("Sheet1").Range("A1").Value = "abc" Then
ActiveSheet.CheckBox15.Visible = True
Else
ActiveSheet.CheckBox15.Visible = False
End If
End Sub

You should reference the sheet directly, not as the active sheet.

Private Sub Worksheets_Calculate()

With Sheet1
.CheckBox15.Visible = False
If .Range("A1").Value = "abc" Then
.CheckBox15.Visible = True
Else
.CheckBox15.Visible = False
End If
End With
End Sub

Let me know if that works or not... It worked for me...
--
HTH...

Jim Thomlinson


"yoram" wrote:

> yoramsk@gmail.com
> Jan 23, 9:22 am show options
>
> From: "yora...@gmail.com" <yora...@gmail.com> - Find messages by this
> author
> Date: Mon, 23 Jan 2006 15:22:13 -0000
> Local: Mon, Jan 23 2006 9:22 am
> Subject: making a checkbox appear and disappear based on a cell's value
>
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
>
> i know this might have been addressed before but i cannot get the
> following code to work. basically i want a checkbox to appear if cell
> A1 is = "abc" and disappear if it is anything else. this checkbox was
> created from the controls toolbar. so i started off the checkbox set
> to invisible and then have an if/then statement to make it visible.
> what am i doing wrong? right now the checkbox will not reappear.
> thanks in advance for any help.
>
>
> Private Sub Worksheets_Calculate()
> ActiveSheet.CheckBox15.Visible = False
> If Worksheets("Sheet1").Range("A1").Value = "abc" Then
> ActiveSheet.CheckBox15.Visible = True
> Else
> ActiveSheet.CheckBox15.Visible = False
> End If
> End Sub
>
>

maybe if you coerced the value from the cell into a string? or format the
cell to text.

Thanks for the prompt response. Unfortunately, the code still doesn't
work for me. Changing the cell's value doesn't make the checkbox
visibile/invisible. I am running 2002 and tried with protection on/off.

A typo in your original code continued into Jim's

> Private Sub Worksheets_Calculate()

Private Sub Worksheet_Calculate()


When you say "Changing the cell's value" if the cell is not a formula
changing it's value will not trigger a calculation event. So use the Change
event

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("a1")) Is Nothing Then
CheckBox1.Visible = UCase(Range("A1") = "ABC")
End If

End Sub

As the code is in a worksheet module everything defaults to the sheet, even
if it's not the active sheet. For clarity could qualify with Me

Regards,
Peter T


"yoram" <yoramsk@gmail.com> wrote in message
news:1138048027.329955.318120@g14g2000cwa.googlegroups.com...
> Thanks for the prompt response. Unfortunately, the code still doesn't
> work for me. Changing the cell's value doesn't make the checkbox
> visibile/invisible. I am running 2002 and tried with protection on/off.
>

Sorry about taking so long... The code that you posted needs to be in the
ThisWorkbook module, and events must be enabled. If it is in thisworkbook
then run this code to reset your events...

Sub test
Application.enableevents = true
end sub

You should also check for where it was set to false and ensure that that
code is working properly.
--
HTH...

Jim Thomlinson


"yoram" wrote:

> Thanks for the prompt response. Unfortunately, the code still doesn't
> work for me. Changing the cell's value doesn't make the checkbox
> visibile/invisible. I am running 2002 and tried with protection on/off.
>
>

Awesome. Many thanks Peter and Jim.

Awesome. Many thanks Peter and Jim.

Sorry... Not in Thisworkbook, but rather in the sheet that is the target...
Also Peter has a better eye than I do. There is a typo in the procedure
name... Monday is getting the best of me here...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Sorry about taking so long... The code that you posted needs to be in the
> ThisWorkbook module, and events must be enabled. If it is in thisworkbook
> then run this code to reset your events...
>
> Sub test
> Application.enableevents = true
> end sub
>
> You should also check for where it was set to false and ensure that that
> code is working properly.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "yoram" wrote:
>
> > Thanks for the prompt response. Unfortunately, the code still doesn't
> > work for me. Changing the cell's value doesn't make the checkbox
> > visibile/invisible. I am running 2002 and tried with protection on/off.
> >
> >

Awesome. Many thanks Peter and Jim.

Obviously one of those typo days -

> CheckBox1.Visible = UCase(Range("A1") = "ABC")

CheckBox1.Visible = UCase(Range("A1")) = "ABC"

(assuming case sensitive condition is not required)

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:uVKuQNGIGHA.2040@TK2MSFTNGP14.phx.gbl...
> A typo in your original code continued into Jim's
>
> > Private Sub Worksheets_Calculate()
>
> Private Sub Worksheet_Calculate()
>
>
> When you say "Changing the cell's value" if the cell is not a formula
> changing it's value will not trigger a calculation event. So use the
Change
> event
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Not Intersect(Target, Range("a1")) Is Nothing Then
> CheckBox1.Visible = UCase(Range("A1") = "ABC")
> End If
>
> End Sub
>
> As the code is in a worksheet module everything defaults to the sheet,
even
> if it's not the active sheet. For clarity could qualify with Me
>
> Regards,
> Peter T
>
>
> "yoram" <yoramsk@gmail.com> wrote in message
> news:1138048027.329955.318120@g14g2000cwa.googlegroups.com...
> > Thanks for the prompt response. Unfortunately, the code still doesn't
> > work for me. Changing the cell's value doesn't make the checkbox
> > visibile/invisible. I am running 2002 and tried with protection on/off.
> >
>
>