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

[yoram]

(e-mail address removed)
Jan 23, 9:22 am show options

From: "(e-mail address removed)" <[email protected]> - 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

 

[Jim Thomlinson]

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...

 

[Paul]

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

 

[yoram]

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.

 

[Peter T]

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

 

[Jim Thomlinson]

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.

 

[Jim Thomlinson]

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...

 

[yoram]

Awesome. Many thanks Peter and Jim.

 

[yoram]

Awesome. Many thanks Peter and Jim.

 

[yoram]

Awesome. Many thanks Peter and Jim.

 

[Peter T]

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