+ Reply to Thread
Results 1 to 5 of 5

Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

    Hi,

    I have a spreadsheet with 100 to 150 checkboxes on it. I have them separated into 3 to 4 checkboxes going down one column with several empty cells between them. I want each group of 3 to 4 checkboxes to only show one checkbox as true at a time. If a checkbox without a check in it is checked, the other checkbox with the check in it become unchecked. I found the code below SetCheckBoxes, and it works great for one set of checkboxes, say Checkboxes 1-3, but when I try using this same function again to duplicate the function for, say for Checkboxes 4-7, I get a “Compile error: Ambiguous name detected: SetCheckBoxes”. How can I make each group of checkboxes work independently from each other? I am very limited to my knowledge of VBA.

    Thank You,

    drm51

    Here is the code I found:

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-28-2013 at 01:25 AM. Reason: Added Code Tags

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    Re: Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

    Could you use "Forms Controls", "Option Buttons" with each Group set in a "Group Box".

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

    Another alternative would be to use the DoubleClick event to cycle a cell through the numbers 0,1,2,3,4
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

    I prefer to use the checkboxes, if I can go that route. I am also sending the code I used to try to make checkboxes 4-7 work, but that's when I get the Error above when I tried it. What changes do I need to make each of these codes unique and work independently of each other.

    Here is that code:

    Please Login or Register  to view this content.
    It's basically the same, except for the checkbox numbers. It lets me run one of them at a time, but not both, or possibly many. I would like to be able to run these codes independently for as many as I need.

    Also mikerickson, I don't really know what you mean to use the DoubleClick event to cycle the cells.

    Thanks again, for your help and input in trying to solve this issue.

    drm51
    Last edited by Leith Ross; 04-28-2013 at 01:26 AM. Reason: Added Code Tags

  5. #5
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Mutiple Checkboxes using SetCheckBoxes Function to change state of several checkboxes

    You have 4 check boxes per group, at most, one of them can be selected at any time. That means there are only 5 possible states for each group.
    Checkbox1, CheckBox2, CheckBox3, CheckBox4 or none. Putting this in the sheet's code module will set it so that double clicking on a cell in column A cycles through 5 states, i.e. the cell value goes from 0 to 1 to 2 to 3 to 4 to 0 etc.

    Please Login or Register  to view this content.
    If you are going to reject the use of option buttons on esthetic reasons, you are unlikely to adopt my solution.

    To improve your code you might try changing this line

    Please Login or Register  to view this content.
    But that would require that the checkboxes be named logicaly and the grouping 4,5,6,7 suggests that they arent (the group 1,2,3 wouldn't fit that pattern)

    I'll reenforce MickG's suggestion that you use option buttons instead of checkboxes. Their inbuilt functionality is superior to coding checkboxes to mimic an option button.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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