 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006making 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...  SignatureHTH... Jim Thomlinson > yoramsk@gmail.com > Jan 23, 9:22 am show options [quoted text clipped - 24 lines] > End If > End Sub 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 > 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. 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 > A typo in your original code continued into Jim's > [quoted text clipped - 23 lines] > > 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. SignatureHTH... Jim Thomlinson > 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... 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... SignatureHTH... 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 [quoted text clipped - 10 lines] > > 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. Awesome. Many thanks Peter and Jim. maybe if you coerced the value from the cell into a string? or format the cell to text. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.