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,

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

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,

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!

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?

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

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.

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.