Hi...this is probably fairly simple for all the awesome excel guru's out there....i have 2 checkboxes labeled AM and PM. I would like for AM to be checked if "AM" is the text in a specific cell, and PM to be checked if "PM" is the text in the same cell. I am ok with using option buttons instead if that is better, but would prefer the checkboxes. Can anyone teach me how to do this? Note that the checkboxes are located on a worksheet not on a user form. Thanks in advance guys.
You don't specify which check box control you are using: Forms or Active-X
This code will work with either type of control as long as the linked cell is set. Change the range reference per your needs. This code goes into the worksheet module of the sheet in which the controls reside. It assumes cell C1 holds the text and cells A1 and A3 are the linked cells.
Please Login or Register to view this content.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
The easiest way to do this would be to use a formula that evaluates to TRUE or FALSE and then set the linked cell property of the checkbox to the cell containing the formula. This applies to activex and forms checkboxes. The formulas could be placed in hidden rows.
Example:
A1 is the cell where "AM" or "PM" are entered.
B1 contains =IF(A1="AM",TRUE,FALSE)
C1 contains =IF(A1="PM",TRUE,FALSE)
Now set the linked cell of the "AM" checkbox to cell B1; C1 for the "PM" checkbox...
This approach will not work as it is easily broken. When a user ticks the check box the formula in the linked cell will be over-written with the check box status. This approach only works if the control and linked cells are locked and sheet is protected.Now set the linked cell of the "AM" checkbox to cell B1; C1 for the "PM" checkbox...
This approach will not work as it is easily broken.
It works just fine.
By all means... protect the worksheet. This was assumed. See the example...
http://cid-ea73b3a00e16f94f.skydrive...cell-value.xls
Thanks for help guys...Unfortunately neither solution is working for me. One thing i neglected to mention is that the AM or PM value is obtained with a formula that will never change. Palmetto this is the code I ended up with
so on my worksheet cell AB23 contains a formula that returns either AM or PM, and AC23 is linked to the AM checkbox, and AD23 is linked to the PM checkbox. Not sure why it doesn't work....but i really dont understand the code, I also tried the following code which didn't work eitherPlease Login or Register to view this content.
Any ideas where I am going wrong gentlemen?? Thanks again for your timePlease Login or Register to view this content.
Tom, actually your solution does work....thanks for that....it is a bit unstable though, however this will be a protected sheet....I would prefer to VBA this if possible, but your solution does accomplish my goal...thanks againYour input is greatly appreciated
The previous code worked fine for me. However, this is a bit cleaner and works equally well. Since you are basing the change on the results of a calculated cell there is no need a target cell.
See attached. Cell D1 is the input cell and C1 is the formula cell.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks