+ Reply to Thread
Results 1 to 13 of 13

Toggle between userform checkboxes for multiple pairs of checkboxes

  1. #1
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Toggle between userform checkboxes for multiple pairs of checkboxes

    Hi All,

    I have Userform to allow the entry of lot and bid information (auction information) into a worksheet from data supplied by the client over the phone. The Userform consists of two columns of textboxes that correspond to a range in the worksheet. Next to each row of checkboxes is a checkbox for 'Either' and a checkbox for 'Or' which enables the user to show whether the client would like to bid on 'Either' lot x 'Or' on lot y. When either is true, the lot text box will have the word 'Either' or 'Or' added to the front of the value textbox. If neither are true, then the lot stands alone. Only one checkbox per row can be true at a time, changing the other to false when checked.

    The problem I have is working out how to change the other checkbox to false with one click when its state is true. I have code that will achieve this in two clicks in the attached - once to change the other checkbox to false and another click to change the clicked checkbox to true. This is demonstrated clearly in the attachment. What I need though is the one click change. Apart from this requirement, the attached sheet functions exactly as it should.

    Further to this, the attached sample is only 10 rows when the actual Userform utilises 35 rows. 35 pairs of checkboxes ends up being very code heavy for the workbook and labour intensive! I would like to know if it is possible to have one macro to execute the checkbox change when called from any of the Either/Or checkboxes. I have deliberately named each textbox and checkbox so that each rows textbox and checkbox number goes up by an increment of 1 for each consecutive row to help facilitate a loop if this is possible.

    My VBA skills are not amazing and I find I tend to over engineer things where much simpler code will do. I'm hoping that is the case here.

    Any help would be greatly appreciated!

    Many thanks,
    Attached Files Attached Files
    Dave C

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,697

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Why not use option buttons rather than check boxes? That way you can easily group them together in pairs and only one can be selected at a time.
    A simple single click solution.

    BSB

  3. #3
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    243

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Use Option Buttons rather than Checkboxes.

    To 'link' 2 together use the GroupName property. Only 1 option in any GroupName can be selected at any one time. click 1 and the other (or others, in that group) will be cleared.

    The only potential issue is you cannot (easily) clear all selected items in a group.

  4. #4
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Thanks BSB and grimes0332 for the idea. I have now experimented with this method and found I would need a third option button to clear the other two which defeats the purpose of having only two selection methods. Both selection methods need to be de-selectable without selecting the other.

    I think I will still go with the original plan of checkboxes if I could figure out how to code the one click select/de-select

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,697

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Use a change event rather than a click event.
    The below code should do what you need. It will allow only one of the two check boxes to be selected at a time but will allow you to unselect too.

    Please Login or Register  to view this content.
    All you have to do it tie in your other code to format the textboxes.

    BSB

  6. #6
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Sometimes you stare at something for so long you just can't see the simple answer! Many thanks BSB. The amended code for a single pair of checkboxes is below for interested parties.
    Please Login or Register  to view this content.
    I am still working on the single macro to handle all Either/Or checkbox events otherwise my file will be too code heavy. Any clues for this from anyone are also appreciated. I will post the code if I mange to work it out.

    Thanks,

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,697

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Happy to help Thanks for the rep points.

    I'm about to leave work now but will have a look at the second part of your request when I get home in a couple of hours.

    BSB

  8. #8
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    243

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Just as an aside, to save your userform being too 'control heavy', you don't need Labels to hold the CheckBox caption, Checkboxes have their own Caption property - saves lining up all those controls.

  9. #9
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Thanks BSB!

    Hi grimes, I had actually used the caption property of the CheckBoxes originally but the font size I used did not line up exactly dead centre with checkbox (horizontal alignment but no vertical alignment) so I had to make do with Labels. Not ideal, but it satisfied my OCD!

  10. #10
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    I've attached my current iteration of the workbook as I try to operate all checkboxes calling from one macro. It needs snagging as I'm having trouble referencing the controls using a variable but I think I'm on the right track...maybe?
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    Ok. My control references are ok but my loop parameters were wrong. i will post my solution tomorrow. thanks to grimes and BSB.

  12. #12
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    As usual, this turned out to be very simple in the end! Code is below and will allow users to make two check boxes act like option buttons with the added advantage of being able to de-select all without a third option button. I've also attached the amended file for reference. Hope someone finds this as useful as I did.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by L plates; 04-25-2016 at 06:31 AM. Reason: Final code correction!!

  13. #13
    Forum Contributor
    Join Date
    10-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    128

    Re: Toggle between userform checkboxes for multiple pairs of checkboxes

    I've updated the code above one last time after realising it had a few flaws. The final (and I mean it!) workbook is attached.

    Apologies for not being more careful with my testing.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Error Checking Multiple Checkboxes in a Userform
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2016, 12:01 PM
  2. Selecting objects with checkboxes/toggle boxes
    By darxide23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2015, 01:48 PM
  3. [SOLVED] Toggle multiple checkboxes (only one checkbox is allowed at a time)
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-14-2015, 06:20 AM
  4. Autofilter with multiple checkboxes selections on userform with vba
    By Sravanmonty in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2014, 08:37 PM
  5. [SOLVED] Userform with multiple checkboxes used for autofilter
    By JorisDDS in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2014, 10:41 AM
  6. Print Sheets based on checkboxes in UserForm, Checkboxes also control another macro
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 34
    Last Post: 03-05-2013, 11:12 AM
  7. Using checkboxes to toggle between cell groups
    By dfairchild19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2013, 03:09 PM

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