Toggling Between Two Checkboxes

[Bawb]

Hello everyone, I am brand new to forums, so I hope I'm doing this correctly?
I found the below instruction to get checkboxes to mimic each other on this site from a 2005 post by Andywiz.
I am wondering if there is a similar way to have the checkboxes do the opposite of each other? That is with a link?
I have 20 checkboxes set up in pairs. They select different forms to print depending on which box is checked. The forms are completely different. One tracks hours for a bid job and the other tracks time and material jobs. These are set up on a time card which tracks up to 10 jobs at one time. I would like either checked box to cancel out the other checkmark when clicked, only for it's associated job. Thus choosing only one sheet or the other to print for each job.
I'm brand new to coding and have worked long hours to make the forms and subroutines which print preview only the selected forms, therefore there is code attached to the checkboxes or visa/versa. I learned, only today, about buttons which already work this way. For my form I would prefer to use checkboxes.

Any help would be appreciated.


".You should be able to set an identical Cell-Link for both checkboxes...

1. Right-click on each checkbox, and select 'Format Control...'.

2. Navigate to the CONTROL tab (far-right I think).

3. Enter a cell address in the 'Cell-Link' box.

4. Do the same for the other checkbox as well, with the same cell address.

Now both the form controls should mimic each other. You could then simply hide the cell displaying the TRUE/FALSE values from the checkbox if you need to."

 

[NoSparks]

I am wondering if there is a similar way to have the checkboxes do the opposite of each other? That is with a link?

No. You'll need to use VBA code.

I have 20 checkboxes set up in pairs.

Where are these 20 check boxes located ?

 

[Bawb]

Hi NoSparks, thank you for responding. They are located on the time card which has 10 rows of jobs, with two checkboxes for each job. One to choose a labor form, if the job is Bid and another to choose a Job Invoice in the job is time and material. Both forms are on individual sheets. I have attempted various macros I could find on the web to no avail. I am brand new at coding, but I am finding it enjoyable.

 

[NoSparks]

OK, so the way I understand things...
the check boxes are on a sheet named "time card"
and the paired check boxes are presumably on the same row, but I have no idea as to what rows,
and the first of the pair is in one column, but I don't know which one,
and the second of the pair is in another column.

Can you narrow things down to the exact answers for the above ?

 

[Bawb]

Perhaps a picture will be worth a thousand words here? Below is the sheet "Time Cards". It currently has data in all cells for building purposes.
When I push button "Clear workbook", it clears all data from unlocked cells, Ink and resets checkboxes to unchecked. Print time related materials button is what I have recently built. It prints only the T&M or Contract paperwork, per checkmark, which is on two other sheets. The checkboxes currently operate normally and all 20 can be checked at one time. I would like for each set of checkmarks for each job, to toggle back and forth, without upsetting the current macro to print.
I'm sure somehow this will need a modification to the current print macro, unless there is a way for the checkboxes to call (2) separate macros?
I hope this helps? Sorry for the lack of information from me. This is my 1st time requesting help in this manner. Thank you for your patience.

 

[NoSparks]

That doesn't show the row numbers or column letters so based on what I see I set up a test workbook putting Form Control check boxes in columns C and D rows 10 to 19.

I have a macro tucked away that I dig out and edit for putting multiple checkboxes on a sheet.

Using this macro automatically inserted checkboxes giving them names that include the cell address they got put in and assigning the macro to be run each time they are clicked.

Private Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, links are NOT cleared.
    .CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.
    Set myRng = .Range("C10:D19")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, Left:=.Left, _
                         Width:=.Width, Height:=.Height) 'click area same size as cell
            CBX.Name = "CBX_" & .Address
            CBX.Caption = ""         'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.LinkedCell = .Offset(0, 0).Address  '<~~~~~ offset to linked cell
            CBX.OnAction = "ChkBoxClick"    'each time clicked
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub

This is the macro run each time any of these checkboxes gets clicked.
The range to work with/from is derived from the name of the clicked check box.

Private Sub ChkBoxClick()
    Dim WhoCalled As String
    Dim WhoCol As String
    Dim rng As Range
   
WhoCalled = Application.Caller
    'MsgBox WhoCalled       'uncomment for info when testing
    'MsgBox Split(WhoCalled, "_")(1)
   
WhoCol = Split(Replace(WhoCalled, "_", "$"), "$")(2)

Set rng = ActiveSheet.Range(Split(WhoCalled, "_")(1))

Select Case WhoCol
    Case "C"
        rng.Offset(, 1).Value = Not rng.Value
    Case "D"
        rng.Offset(, -1).Value = Not rng.Value
End Select

End Sub

Test things on a copy of your workbook.
Better yet, put these in a new blank workbook and just play with it first.

Hope that helps, any questions just ask.

 

[Bawb]

I put this into a new blank workbook to check it out.
It adds checkboxes perfectly, but when I click them I get a "Run-time Error '13' Type Mismatch"?

 

[NoSparks]

Works for me, otherwise I wouldn't have posted it.

What line do you get the mismatch error on?
If you uncomment the message box lines do they execute?

 

[Bawb]

I ran the Insert_Form_Checkboxes() to renew the checkboxes, left all lines commented as posted and the checkboxes work. I uncommented the line 'MsgBox Split(WhoCalled, "_")(1) and again it wouldn't work afterwards, even when commented back. So I ran Insert_Form_Checkboxes() again with it commented and it works!
I get the run time error when I try to run it with the play button in the vba window.

Bottom line, It works. Thank you for your time with this. I really appreciate your help!

Now where do I click to give you credit for solving this?

 

[NoSparks]

I get the run time error when I try to run it with the play button in the vba window.

That's because there is no caller when you do that, ie: no checkbox that is calling the sub, and everything in the sub is dependent on the caller name.
If you notice, I made that macro Private, that prevents it from showing up in the Alt+F8 macro dialogue window where an attempt to run it would cause the exact error you experienced.
I'll admit I didn't try running it from the dialogue window, I just knew it shouldn't be.

Any way, glad you got it sorted.
Please mark post #6 as the answer, thanks.