Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Insert checkbox using button

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Location
    El Paso, TX
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert checkbox using button

    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!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Insert checkbox using button

    You will no doubt need to make changes to the following code based on your actual requirements, but this should get you started...

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

  3. #3
    Board Regular
    Join Date
    Sep 2015
    Location
    El Paso, TX
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert checkbox using button

    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?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Insert checkbox using button

    Do you want the code to run when when you click on the commandbutton?

  5. #5
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Insert checkbox using button

    And do you already have code for that commandbutton?

  6. #6
    Board Regular
    Join Date
    Sep 2015
    Location
    El Paso, TX
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert checkbox using button

    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 by harveya915; Apr 4th, 2018 at 08:41 PM.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Insert checkbox using button

    Yes, that exactly right....

  8. #8
    Board Regular
    Join Date
    Sep 2015
    Location
    El Paso, TX
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert checkbox using button

    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?

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Insert checkbox using button

    Try...

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

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,818
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert checkbox using button

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •