Checkboxes linked to spreadsheet - quicker method?

[RockEd]

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:

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:

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!

 

[JGordon11]

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:

    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]

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:

    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]

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]

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.

combine update subs

Hello excel friends, I have 12 textboxes asking for a simple integer. there is a label following, where the values of each textbox is added up. This is all very simple, but the code below does look a bit primitive to me to have to put the _change of each textbox in a separate subroutine. I...

www.mrexcel.com

 

[RockEd]

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.

combine update subs

Hello excel friends, I have 12 textboxes asking for a simple integer. there is a label following, where the values of each textbox is added up. This is all very simple, but the code below does look a bit primitive to me to have to put the _change of each textbox in a separate subroutine. I...

www.mrexcel.com

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

 

[RockEd]

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.

combine update subs

Hello excel friends, I have 12 textboxes asking for a simple integer. there is a label following, where the values of each textbox is added up. This is all very simple, but the code below does look a bit primitive to me to have to put the _change of each textbox in a separate subroutine. I...

www.mrexcel.com

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.

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]

In my 'UserForm_Activate' (I've used this instead of UserForm_Initialize for other reasons) - I've used the following 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]

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

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

 

[RockEd]

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

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!