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!

 

[RockEd]

I've worked it out....

Private Sub UserForm_Activate()
Dim i As Integer


With ActiveCell
For i = 1 To 25 'number of checkboxes 
    Me.Controls("CheckBox" & i).ControlSource = Range("Checkbox" & i).Offset(.row - 1).Address
 Next i
 
End With
    CreateEventHandlers
End Sub

thanks all

 

[mikerickson]

Hmm. Its not on the Object Browser. But it if works for you rather than the .Tag great.

 

[GWteB]

@RockEd, let's start at the beginning. Do you already have checkboxes on your userform? If so, don't use its ControlSource property, that will only bother us at a later stage.
Do the CheckBoxes have to be linked to a certain column or is it randomly? What does your data look like? I'm trying to get the full picture of it, but it seems I don't succeed yet.

EDIT: is it by any change a modeless userform?

 

[GWteB]

I've worked it out....

Well done!

 

[RockEd]

@RockEd, let's start at the beginning. Do you already have checkboxes on your userform? If so, don't use its ControlSource property, that will only bother us at a later stage.
Do the CheckBoxes have to be linked to a certain column or is it randomly? What does your data look like? I'm trying to get the full picture of it, but it seems I don't succeed yet.

EDIT: is it by any change a modeless userform?

- No these are the only checkboxes.
- They need to be linked to a column, yes.
- I only need true or false as the answer.

The purpose they serve is to help build a series of buttons that the user can press to sort the data.

e.g. checkboxes 1-8 refer to the type of 'case' you are working on; 9-15 refer to something else; and so on.

I will then use auto-filter to build some pre-set filters so that the user can drill down on the data quickly, e.g.

User: "show me cases that contain XYZ, and ABC" - that would equate to a "true" in column 3 and True in column 12.

--
In order for the data to be built up, the userform will write in values into the respective columns. I might make each row = to false first (although I have a feeling the spreadsheet might get a bit heavy if I do that!)

Please say if that's not making sense and i will re-write.

Edit: no it's not modeless.

 

[GWteB]

The moment I submitted my post #13 I hadn't read your post #11 ("I've worked it out ...") yet.
If you're happy, so am I When further assistance is required, let us know.

 

[RockEd]

The moment I submitted my post #13 I hadn't read your post #11 ("I've worked it out ...") yet.
If you're happy, so am I When further assistance is required, let us know.

haha I thought that might be the case, but wasn't sure!

I've edited it a bit more and now don't need to fill my sheet with a load of "false" entries...

Where range("false_data") - refers to a cell that just has "false" written in it.

Dim i As Integer, CheckboxANS As String

With ActiveCell
    For i = 1 To 23
        If Range("Checkbox" & i).Offset(.row - 1) = "" Then
            CheckboxANS = Range("False_data").Address
            
        Else
            CheckboxANS = Range("Checkbox" & i).Offset(.row - 1).Address
            
        Me.Controls("CheckBox" & i).ControlSource = CheckboxANS
        End If
    Next i
 End With