Insert checkbox using button

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
91
I'm new to this so bare with me.

I have an excel sheet with data on it in columns B-G. The amount of Rows with data will vary, beginning from row 6-?, but always continuous, no rows will be empty.

I would like to click a button and have checkboxes inserted in columns H,I,J only on the rows with data on them.

P.S. When people on here provide codes for me, I ask them to provide a small explanation as to what the code does, I then save all these codes for future reference. If you could please do the same, I would really appreciate it.

This is an example, the green is the description...
'Make Sheet1 active
Sheet1.Activate

'Transfer information
ActiveCell.Value = UserForm2.TextBox4.Value
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Value = UserForm2.ComboBox2.Value
ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Value = Now

Much Thanks!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,035
You will no doubt need to make changes to the following code based on your actual requirements, but this should get you started...

Code:
[COLOR=darkblue]Sub[/COLOR] AddCheckBoxes()
    
    [COLOR=darkblue]Dim[/COLOR] oChkBx [COLOR=darkblue]As[/COLOR] CheckBox
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=green]'Find the last used row in the active sheet[/COLOR]
    [COLOR=darkblue]With[/COLOR] ActiveSheet.UsedRange
        LastRow = .Rows.Count + .Rows(1).Row - 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'Loop from Row 6 to the last row[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = 6 [COLOR=darkblue]To[/COLOR] LastRow
        [COLOR=green]'Add a checkbox in Column H of the current row[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=Cells(i, "H").Left, Top:=Cells(i, "H").Top, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Caption = "" [COLOR=green]'remove caption from checkbox[/COLOR]
            .Value = xlOff [COLOR=green]'make sure checkbox is unchecked[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=Cells(i, "I").Left, Top:=Cells(i, "I").Top, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Caption = ""
            .Value = xlOff
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=Cells(i, "J").Left, Top:=Cells(i, "J").Top, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Caption = ""
            .Value = xlOff
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Hope this helps!
 
Last edited:

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
91
Quick questions, I'm assuming this will go in the Sheet1 Code window and not in the UserForm1 Code window?
Secondly, In The Sheet1 code window I have:
Private Sub CommandButton1_Click()
Do I leave that there and just paste the code underneath? Or just remove the Private word?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,035
Do you want the code to run when when you click on the commandbutton?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,035
And do you already have code for that commandbutton?
 

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
91
yes, I want it to run when i click on the button.
I have already created the button. The only code that shows up is in the Sheet1 Window with:
Code:
Private Sub CommandButton1_Click()
However, I did play around with it a bit, I copy/pasted your code right underneath the code mentioned above, and removed the part from your code that reads
Code:
Sub AddCheckBoxes()
This Seems to be working so far, unless there is another "Correct" way to do it.

Another quick question: what part do I alter in order to center the checkbox inside the cell? If I can take a quick guess, would it be replace the parts that say Left?
 
Last edited:

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
91
I tried changing "LEFT" to "CENTER" and it doesn't seem to help.
Also, when I change the Width and Height, it only seems to move the boxes down.
Any Idea?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,035
Try...

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    
    [COLOR=darkblue]Dim[/COLOR] oChkBx [COLOR=darkblue]As[/COLOR] CheckBox
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=green]'Find the last used row in the active sheet[/COLOR]
    [COLOR=darkblue]With[/COLOR] ActiveSheet.UsedRange
        LastRow = .Rows.Count + .Rows(1).Row - 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'Loop from Row 6 to the last row[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = 6 [COLOR=darkblue]To[/COLOR] LastRow
        [COLOR=green]'Add a checkbox in Column H of the current row[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=0, Top:=0, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Left = Cells(i, "H").Left + (Cells(i, "H").Width - .Width) / 2 [COLOR=green]'center horizontally[/COLOR]
            .Top = Cells(i, "H").Top + (Cells(i, "H").Height - .Height) / 2 [COLOR=green]'center vertically[/COLOR]
            .Caption = "" [COLOR=green]'remove caption from checkbox[/COLOR]
            .Value = xlOff [COLOR=green]'make sure checkbox is unchecked[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=0, Top:=0, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Left = Cells(i, "I").Left + (Cells(i, "I").Width - .Width) / 2
            .Top = Cells(i, "I").Top + (Cells(i, "I").Height - .Height) / 2
            .Caption = ""
            .Value = xlOff
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChkBx = ActiveSheet.CheckBoxes.Add(Left:=0, Top:=0, Width:=24, Height:=16)
        [COLOR=darkblue]With[/COLOR] oChkBx
            .Left = Cells(i, "J").Left + (Cells(i, "J").Width - .Width) / 2
            .Top = Cells(i, "J").Top + (Cells(i, "J").Height - .Height) / 2
            .Caption = ""
            .Value = xlOff
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,391
Would you care to tell us what you want to happen when you click on these check boxes?

There are a lot of ways to perform certain task without needing a button to perform a task

Writing code for each of these check boxes could be a task.

Why not use a code where if you double click on a cell or enter some data into a cell something will happen.

This could be done with just a short amount of code and would not have to written for each cell.

For example if B1.value=Me then C1.value=You
 

Forum statistics

Threads
1,084,962
Messages
5,380,841
Members
401,699
Latest member
ijazkhan01

Some videos you may like

This Week's Hot Topics

Top