Checkboxes linked to spreadsheet - quicker method?

RockEd

New Member
Joined
Aug 13, 2021
Messages
40
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!
 

RockEd

New Member
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
I've worked it out....

VBA Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,097
Hmm. Its not on the Object Browser. But it if works for you rather than the .Tag great.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
@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?
 

RockEd

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

ADVERTISEMENT

@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

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 13, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
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,152,389
Messages
5,769,798
Members
425,572
Latest member
ja189704

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