Checkboxes linked to spreadsheet - quicker method?

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have made a userform with about 25 checkboxes. The userform's contents has information on it with regards to what is happening on the worksheet - within the row the user is currently on.

The checkboxes should populate true/false in the respective column of the row the user is on - depending on whether the box is ticked or unticked (of course!) [so each checkbox has its' own column - and the result is true/false for each of them.

To be clear - when the userform is brought up, the boxes should still be ticked/untick depending on what the sheet has written for that row (true/false).

However, this is taking me sometime to build up and I am wondering whether there is a quicker method.

I have had to build up the value of the checkbox in the "userform_activate" and then for each of the "Checkbox[number]_click, I have to update the spreadsheet with its' value. I appreciate that once I've done writing the code it's done and i won't have to go back to it (especially as I have 'named' each of the columns). Can I use a listbox or something else to quickly jump for one box to another?

An example of what I have had to do:

VBA Code:
Private Sub UserForm_Activate()
Dim RowNumber As Integer, Alpha As String
With Activecell
RowNumber = .Row

        Alpha= ActiveSheet.Cells(.Row, Range("Alpha").Column)
Checkbox1 =Alpha
End with
End sub

Then in the checkbox1 click:

VBA Code:
Private Sub CheckBox1_Click()
Dim RowNumber As Integer, ColNumber As Integer

With ActiveCell
        RowNumber = .Row
End With

With Range("Alpha")
    ColNumber = .Column
End With

Cells(RowNumber, ColNumber).Value = Checkbox1.Value

Am I doing anything wrong here or is there a quicker method to this madness?

thank you!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
473
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I don't think you need 25 CheckBox_Click event procedures. When the user closes the form with OK or Apply or however you have it set up, do the copying of the checkbox values to the spreadsheet by looping though the userform controls.

If your userform is named userform1 and the range you want to write the data to is RangeToWriteData and your checkboxes were the first twenty-five controls you added, then perhaps something like:

VBA Code:
    Dim A(1 To 1, 1 To 25) As Boolean   'to write to a row
    For i = 0 To 24
        A(1, i + 1) = UserForm1.Controls.Item(i).Value
    Next

    RangeToWriteData = A

In the immediate window you can type

?userform1.controls.item(x).name

where x is the number you want to check, make sure your checkboxes are controls 0 through 24. If not adjust accordingly.
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I don't think you need 25 CheckBox_Click event procedures. When the user closes the form with OK or Apply or however you have it set up, do the copying of the checkbox values to the spreadsheet by looping though the userform controls.

If your userform is named userform1 and the range you want to write the data to is RangeToWriteData and your checkboxes were the first twenty-five controls you added, then perhaps something like:

VBA Code:
    Dim A(1 To 1, 1 To 25) As Boolean   'to write to a row
    For i = 0 To 24
        A(1, i + 1) = UserForm1.Controls.Item(i).Value
    Next

    RangeToWriteData = A

In the immediate window you can type

?userform1.controls.item(x).name

where x is the number you want to check, make sure your checkboxes are controls 0 through 24. If not adjust accordingly.
Okay that’s perfect thanks, I’ll give that a go!
What about the activate procedure - am I ‘reading’ the data in an efficient way?
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Okay that’s perfect thanks, I’ll give that a go!
What about the activate procedure - am I ‘reading’ the data in an efficient way?
I’ll just add that each of the names cells are in the adjacent column cells to each other in the excel sheet
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Another approach could be to implement a custom event handler class. All check boxes then will share the same code.
An example regarding text boxes can be seen over here and is quite easily to adjust.

 
Solution

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Another approach could be to implement a custom event handler class. All check boxes then will share the same code.
An example regarding text boxes can be seen over here and is quite easily to adjust.

Oh wow this looks great. I will try this first. Thank you
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another approach could be to implement a custom event handler class. All check boxes then will share the same code.
An example regarding text boxes can be seen over here and is quite easily to adjust.

Okay I'm just getting to work on this now - in the private sub - TextBoxGroup_Change, I've added an offset to get the right position on the row.

VBA Code:
Private Sub TextBoxGroup_Change()
With ActiveCell
    ' method 1) as per MrExcel post: usage of a Named Range which name equals the name of the TextBox
    If this.Sht.Parent.Name = ActiveWorkbook.Name Then
        On Error Resume Next
        Range(TextBoxGroup.Name).Offset(.Row - 2).Value = TextBoxGroup.Value
    Else
        ' some other workbook is active
        ' therefore the expected Named Ranges will be out of scope > do nothing
    End If
End With

End Sub

(I haven't yet change it to a checkbox but I imagine that should be straight forward).

However... it will update the cell easily, but doesn't grab the cell value upon the initialize - so the textbox (in your example) is blank when the userform opens. Is it simple to update it to show the cells' value (bearing in mind, in my example, I have 25 checkboxes!).

Thank you
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
In my 'UserForm_Activate' (I've used this instead of UserForm_Initialize for other reasons) - I've used the following code:

VBA Code:
With ActiveCell
    Me.CheckBox1.ControlSource = Range("Checkbox1").Offset(.row - 1).Address
End With

I guess I just repeat it 25 times...

@GWteB - can you think of anything better than this?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,209
There isn't a .ControlSource property for a Userform checkbox, but there is a .Tag property. What I might do is put the column number as the default value for each checkbox and then either loop or use a class to implement something like this

VBA Code:
ActiveCell.EntireRow.Cells(1, Val(CheckBox1.Tag)).Value = CheckBox1.Value
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
There isn't a .ControlSource property for a Userform checkbox, but there is a .Tag property. What I might do is put the column number as the default value for each checkbox and then either loop or use a class to implement something like this

VBA Code:
ActiveCell.EntireRow.Cells(1, Val(CheckBox1.Tag)).Value = CheckBox1.Value
Thanks but I think you're mistaken - there is a control source property... the line of code I wrote above works, I'm just wondering whether I need to write it out 25 times for each of my checkboxes!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,563
Messages
5,838,125
Members
430,529
Latest member
PaperBoi5870

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top