Insert checkbox using button

H

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
141
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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:
Upvote 0
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?
 
Upvote 0
Do you want the code to run when when you click on the commandbutton?
 
Upvote 0
And do you already have code for that commandbutton?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
228
Peter_SSs
Peter_SSs
B
  • Question Question
link a VBA checkbox to a Cell to change value
Replies
6
Views
674
NoSparks
N
C
  • Solved
VBA macro: insert row if condition is met and apply formulae
Replies
3
Views
375
CXsjr
C
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top