Excel VBA on button click toggle value of checkbox in one row


I would like to create a button that will toggle the values of either true or false of all checkboxes within the entire row.

I have multiple rows of checkboxes which i use to calculate values

J NUnicorn wranglerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Do you have a button for each row of checkboxes?

What type of checkboxes are they, Forms or ActiveX?
J NUnicorn wranglerAuthor Commented:
They are form checkboxes

Yes,i have one button for each row of checkboxes
J NUnicorn wranglerAuthor Commented:
Each row has a partial amount of data

ie. the row does not expand from end to end
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

J NUnicorn wranglerAuthor Commented:
!!! WARNING - Im not familiar with VBA its actually my first time playing with i. I am familiar with other programming languages

 Private Sub CommandButton3_Click()
    'Set variable
    Dim b As Boolean

    'Toggle checkBoxes for PST
    If CommandButton3.BackColor = "&H8000000F" Then
        CommandButton3.BackColor = "&H80000005"
         b = False
        CommandButton3.BackColor = "&H8000000F"
         b = True
    End If
    'Adjust checkboxes
    With Rows(Range("PST_TOGGLE").Offset(0, 1), Range("OE_END").ColumnIndex)
        .Value = b
    End With
End Sub

Open in new window

"PST_TOGGLE" is a named cell at the start of the range of checkboxes
"OE_END" is another named cell on the last column of data

i was attempting to get the row value and column start value from "PST_TOGGLE" and the ending column value from "OE_END"

NorieAnalyst Assistant Commented:
Still not sure about your setup but have a look at the attached workbook.

On Sheet1 each row of checkboxes can be toggled by clicking the button on the same row in column F.
J NUnicorn wranglerAuthor Commented:

I dont have the buttons on the same row in your example but i could put them before the checkboxes on the same row.
That being said i would prefer to keep the options where i have placed them

Here is a minified copy of what i am working with.

The larger one has 12 months

NorieAnalyst Assistant Commented:
The workbook I posted was just an example.:)

As you might have figured out the buttons are tied to the checkboxes via their shared row which is accessed via TopLeftCell.

In your workbook are the GST and PST checkboxes always on row 15 and 16 respectively?

If that is the case add this to the code for the GST button (CommandButton2_Click).
    For Each chk In Sheets("Transactions").CheckBoxes
        If chk.TopLeftCell.Row = 15 Then
            chk.Value = IIf(chk.Value = 1, -4146, 1)
        End If
    Next chk

Open in new window

You can use the same code for the PST button (CommandButton3_Click), just replace the 15 with 16 in the If statement.

Note, if the checkboxes aren't always on rows 15 and 16 we should be able to introduce something to locate which row they are on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J NUnicorn wranglerAuthor Commented:

I slightly adjusted the code

'Set variable
    Dim b As Boolean, chk As CheckBox
    'Toggle CheckBoxes for GST
    If CommandButton2.BackColor = "&H8000000F" Then
        CommandButton2.BackColor = "&H80000005"
        b = False
        CommandButton2.BackColor = "&H8000000F"
        b = True
    End If
    'Loop through checkbox
    For Each chk In ActiveSheet.CheckBoxes
        If chk.TopLeftCell.Row = 16 Then
            chk.Value = b
        End If
    Next chk

Open in new window

Is there a way to determine the row by the named cell "PST_TOGGLE"?
NorieAnalyst Assistant Commented:
You can use Range("PST_TOGGLE").Row to get the row for the named range 'PST_TOGGLE', so you could replace 16 with that.
If chk.TopLeftCell.Row = Range("PST_TOGGLE").Row Then

Open in new window

J NUnicorn wranglerAuthor Commented:
Thanks a bunch!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.