+ Reply to Thread
Results 1 to 37 of 37

Make A Cell A Clickable Check Box

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Question Make A Cell A Clickable Check Box

    I have a square cell (A1) on my sheet that is used solely for the purpose of containing a checkmark, or not. I use the letter P in the font WingDings 2 to give me the checkmark.

    My question? Is there any way (besides the Developer/Insert/Check Box option) to make this cell clickable? So that one click would insert the letter P in the font WingDings 2, and another click would erase it?
    Last edited by swordswinger710; 03-01-2012 at 04:34 PM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Make A Cell A Clickable Check Box

    Hi swordswinger710
    See this link
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=879
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    In the sheet module place this code:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Make A Cell A Clickable Check Box

    hi swordswinger710, do you mean sort of that?
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Make A Cell A Clickable Check Box

    You might also wanna add a range("XX").select command just before the end if, otherwise users can click in A1 to put the tick mark in but it'll still be selected.....so they'll be able to type in it.

  6. #6
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    I think JBeaucaire's solution will work the best for my situation, but yes, swoop99's comment is proving to be correct - what exactly do I type before the 'End If' to solve this?

    Also, could I make this code work for multiple cells on the sheet, like for B1, and C1? I'm not sure how or what to duplicate in the macro.

    Thank you all so much for your valuable input so far!

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    Well, the "P" thing is considered "what I want to type", so my version doesn't include the expectation you will type anything else, just click on the cells.

    Here's a syntax you can add to:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you JBeaucaire, that allows me to specify multiple cells as check boxes. However, when I click a cell and it either enters or removes the checkmark, the cell still remains selected; and I have to click elsewhere then back on it again to add or remove the checkmark.

    Are we able to have to cell deselect immediately after it's clicked?

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    Deselect to where?

    This will select the next cell down:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 02-24-2012 at 09:10 AM.

  10. #10
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Hmm, I did not think of that. As it happens, the next cell down for all my checkboxes is quite inconvenient. Are we able to have the cells deselect upon being clicked, then immediately selected again? Just so no useless cells are being selected afterwards, and I am still able to click in the same cell again if I want to - without having to select something else?

  11. #11
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Make A Cell A Clickable Check Box

    well it is just an idea as I'm not aware of your limitations on the actual file.

    As soon as worksheet events are regular, I mean the code would fire up every time you change selection on the worksheet, I would suggest to use a different way, though you are the one to decide if it suits.

    I used rectangular drawing object that fully covers the cell you'd like to make sort of checkbox, transparency is 100%, a simple code assigned to it (post #4). If the cell behind is empty it puts P else clears it out. The main advantage of it is that it will work only at user click and will not test any cell for conditions set in events code. Plus it does not depend on turning on/off of enable events.

    Another option is to use textbox drawing object that will hold the sign itself not "touching" any cell at all. See attached
    Attached Files Attached Files

  12. #12
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you, watersev. What does seem to work best in my case is where the actual cell is used as the checkbox and not a box drawn over the cell. I have many sheets with many cells that need to be used for this purpose. Do you know of any way to have the cell 'refresh', so to speak, after it is clicked (like your drawn checkboxes); thereby allowing it to be re-clicked without the user having to click elsewhere and then back again?
    Last edited by swordswinger710; 02-24-2012 at 11:07 AM.

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Make A Cell A Clickable Check Box

    Hi swordswinger710
    This code is adapted from the link I referenced in post #2. It does require a double click but it appears to do as you require.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    jaslake, thank you! This may be the solution.

    I was able to edit the code to use my preferred checkmark as follows:
    Please Login or Register  to view this content.
    One question remains, however. Can this code be modified to check the cell on the first click as opposed the second? The reason I didn't pursue your method in the very beginning was due to the fact that I needed to double-click, which will be very tedious on the sheets where I use many cells as checkboxes.

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Make A Cell A Clickable Check Box

    Hi swordswinger710
    You can do so with a single Right Click
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Right-clicking with your latest code doesn't enter a checkmark for me - it brings up options for the cell. So, do you all think that one click isn't doable? JBeaucaire's code was the closest so far.
    Last edited by swordswinger710; 02-24-2012 at 12:05 PM.

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Make A Cell A Clickable Check Box

    Hi swordswinger710
    It works for me...see attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you jaslake, your attachment worked, at least for the one right-click.

    And I know I asked this a million times - but why can't this be done with one nice, normal left-click?

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Make A Cell A Clickable Check Box

    Hi swordswinger710
    Far as I know, God didn't make a single left click change event.

  20. #20
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Lol! Alrighty then. JBeaucaire's code will have to do until someone discovers otherwise. Thank you for all your help!

  21. #21
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    This approach can be adapted to "select" an offset cell in any direction you choose...but there HAS to be a selected cell somewhere.

    Please Login or Register  to view this content.

    ....you get the idea. Pick one that irritates you the least.

  22. #22
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    ...and there isn't any code that can select another cell then jump back to the original one right away, so it's not even noticeable? Otherwise, I think I'll use your code as it is in this state..

    Please Login or Register  to view this content.
    ..and simply hope nobody gets frustrated upon trying to un-check a cell they didn't mean to check.

  23. #23
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Make A Cell A Clickable Check Box

    here's an example tht shows 3 ways of doing this
    Attached Files Attached Files
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  24. #24
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you for your input, royUK! However, none of the cells in your three examples can be clicked repeatedly to have the checkmark removed and added - they all still need the user to click elsewhere and then back again to have any effect.

  25. #25
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    Something from post #21 should be acceptable. Try variations out and see which is the friendliest to your work process. Try other offsets.

  26. #26
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: Make A Cell A Clickable Check Box

    Why mess around with offsets? Just select another cell by name.

    I do something very similar to this in a project I'm working on, except I switch between 2 different fonts and 4 states, essentially blank, 1, 2 and tick. On each user input I select cell A1 as a matter of course, as it's standard procedure for me to hide columns A & B on every worksheet I ever design purely so I've got a hidden area to do stuff in that users won't ever see.

    Your code can be simpler as you only want either blank or tick:

    Please Login or Register  to view this content.
    Last edited by swoop99; 02-24-2012 at 09:19 PM.

  27. #27
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you swoop99. That's an excellent point. I tried to use your code, but it doesn't prove as useful in my situation as the code in Post #22 is. That is a fairly good alternative, though, thanks again.

  28. #28
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    I ran into an issue with this code:

    Please Login or Register  to view this content.
    I've been trying to add other cells to this code which I need this function to work in, however I'm having issues adding merged cells, such as I10:I11. How can I add these so they work as well?

  29. #29
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    1) don't merge cells... Format as "Center across Selection" for the same appearance without all the extra plumbing required to constantly code around merged cells.

    2) One shot only.... (already indicated how much I do not support merged cells)

    Please Login or Register  to view this content.

  30. #30
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Considering how much you don't support merged cells, that worked marvelously.

    One (hopefully) more question: Is there some way of converting this:

    Please Login or Register  to view this content.
    ..to something more editable-friendly like this?

    Please Login or Register  to view this content.
    Of course, that probably makes no sense to Excel whatsoever, but do you see what I'm trying to do? I have oodles of cells I need to manually enter in the macro, and any little thing to speed it up would help a ton.

  31. #31
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    Please Login or Register  to view this content.

  32. #32
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you so much, JBeaucaire, that seems to do the trick. I appreciate all your help!

  33. #33
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  34. #34
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    Thank you JBeaucaire, I am aware of that, however, I have left this issue intentionally unsolved as I haven't received the one-click solution I set out to find.
    There is so much good in the worst of us,
    And so much bad in the best of us,
    That it hardly behooves any of us
    To talk about the rest of us.

  35. #35
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Make A Cell A Clickable Check Box

    There isn't one realistically. There is no click event for a range so your options would involve subclassing the Excel window to intercept mouse events (generally unstable) or adding a low level mouse hook triggered by selecting one of the cells of interest. Neither option would be recommended IMO.
    I don't think the mods will appreciate you leaving the question open forever.

    Of course, if it's only a few cells, you could use actual checkbox controls.
    Good luck.

  36. #36
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Make A Cell A Clickable Check Box

    Part of the forum environment is to provide usable solutions to goals, not exact matches to envisioned features. Keeping an open mind to what is offered and working is an expected characteristic of all around here. I can't imagine there aren't other parts of your project that now need attention.

    I'm also not sure how this doesn't satisfy a "one-click" solution since the Selection_Change event does activate on a single click.

  37. #37
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Make A Cell A Clickable Check Box

    You know what? You guys are right. Jbeaucaire's solution did work as a one-click solution, despite the fact that once you click, you can't click again, but I'm satisfied. And watersev had the total solution if drawn checkboxes had been the route I wanted to take. Thank you to everyone involved in this matter. It was awesome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1