Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Insert checkbox using button

  1. #11
    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

    Thank you! It did center it vertically but not horizontally. It also made the boxes bigger.
    Allow me to tinker with it for a while. If I get stuck, I shall be back. If I solve it...then I appreciate all your help!

  2. #12
    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

    Quote Originally Posted by My Aswer Is This View Post
    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
    Well, you're right, I might be overthinking the whole process. Here is a brief description of my project.

    I work in an insurance office. We use a database software that stores all of our clients information. through this software i can export data on to an excel sheet. I then copy the data from that sheet and paste it to another sheet which I already have formated to fit my specific needs (a template). We use this data to perform specific tasks. Hence the checkboxes. When a task is completed i check the box. And since our data can range from 1 row (1 client) to 500 rows, i figured a code for inserting checkboxes only when data was present on the row would be the easiest way.

    Hope this explains a little.

  3. #13
    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

    Quote Originally Posted by Domenic View Post
    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
    So, how do i explain this. You know when you insert a shape, you click on it and then a box with points surrounds your shape. For example, if you insert a circle, when you select it it gets surrounded with a square with points in every corner and points between those corners.

    I noticed that the height and width only makes this selection square bigger or smaller, depending on the size that you input. It does not correspond to the width and height of the actual check box.

    I managed to center the check box by changing the 2 in 'center horizontal to a 3.

    This is all i was able to observe.

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

    Default Re: Insert checkbox using button

    Ok. You said:

    Hence the checkboxes. When a task is completed i check the box. And since our data can range from 1 row (1 client) to 500 rows, i figured a code for inserting checkboxes only when data was present on the row would be the easiest way.

    so when you check the checkbox then what happens

    Do you want all these rows with checkboxes checked copied to another sheet?
    Or do you just want a checkbox checked as a indicator when
    a task is completed

    You also said:
    only when data was present on the row

    present in what column on the row.

    So if you enter data in column "B" for example you want the checkbox checked?

    Last edited by My Aswer Is This; Apr 4th, 2018 at 10:48 PM.
    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"

  5. #15
    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

    I noticed that the height and width only makes this selection square bigger or smaller, depending on the size that you input. It does not correspond to the width and height of the actual check box.
    Yes, the CheckBox object includes everything within the "selection square", which includes the actual check box and the caption area. And so changes to its properties affects the object itself, not the actual check box.

  6. #16
    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

    Quote Originally Posted by My Aswer Is This View Post
    Ok. You said:

    Hence the checkboxes. When a task is completed i check the box. And since our data can range from 1 row (1 client) to 500 rows, i figured a code for inserting checkboxes only when data was present on the row would be the easiest way.

    so when you check the checkbox then what happens

    Do you want all these rows with checkboxes checked copied to another sheet?
    Or do you just want a checkbox checked as a indicator when
    a task is completed

    You also said:
    only when data was present on the row

    present in what column on the row.

    So if you enter data in column "B" for example you want the checkbox checked?

    When a check box is checked, nothing happens.

    Let me try to explain the process a little more in detail.

    I work in insurance, so I use a software that helps me manage all my clients accounts policies.

    From this software I am able to export to excel my client's data. The sheet that we export to, I shall call it "Sheet A". The information that i download is: Name, Effective Date, Expiration Date, Policy #, Company, and Type.

    I created another excel sheet, which I will call "Sheet B". This sheet is already formated with all the settings that I need, for example: Font, Font size, cell sizes, headings, etc...

    I copy the information from Sheet A and paste it on to Sheet B. I find this much easier, as to just applying all the settings that I need to Sheet A. 1 client's data (which we can call record) will use up one row. Any given month I can have as many as 500 records.

    So now back to Sheet B. I use the headings listed above on columns B-G. I added 3 more headings on columns H, I, J (Email, Phone, Mail). These are tasks that need to be performed. This is why I need check boxes for each of these 3 headings for each record. This is where I figured it would be useful to have a code for it to recognize exactly how many records I have and insert the check boxes automatically for each record.

    When the check boxes are checked it is only to signify that the task has been completed. However, I would like it, that when a box is checked I can have the time completed show up on a cell next to that box.

    And this pretty much sums it all up.

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

    Default Re: Insert checkbox using button

    So then why could we not do this.

    Assuming by Record you mean Row

    When all is completed on row (2)
    You double click on A2 this cells background color turns yellow signifying this row is complete.
    And when you double click A2 todays date is entered into B2 this date is permanent it will not change when tomorrows come.

    See this type way of doing things requires no check boxes and just about 5 lines of code that would apply to all cells.
    Last edited by My Aswer Is This; Apr 5th, 2018 at 01:21 PM.
    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"

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

    Default Re: Insert checkbox using button

    How do you do this:
    I copy the information from Sheet A and paste it on to Sheet B. I find this much easier, as to just applying all the settings that I need to Sheet A. 1 client's data (which we can call record) will use up one row. Any given month I can have as many as 500 records.

    Do you do this manually or do you have a Vba script which does this for you.

    If you do it manually we could write you a script to do this for you.
    Again something as easy as double clicking A2 and this row is copied to sheet B

    If you want a script to do this we need a both sheet names the copy from sheet name and the copy to sheet name.

    Use a name like Master and Stored do not just use sheet names like sheet1 and sheet2.

    This way at a quick glance you know what these sheet are for.
    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"

  9. #19
    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

    Quote Originally Posted by My Aswer Is This View Post
    So then why could we not do this.

    Assuming by Record you mean Row

    When all is completed on row (2)
    You double click on A2 this cells background color turns yellow signifying this row is complete.
    And when you double click A2 todays date is entered into B2 this date is permanent it will not change when tomorrows come.

    See this type way of doing things requires no check boxes and just about 5 lines of code that would apply to all cells.

    I like this idea! If I could just double-click on a cell, to change color and perhaps enter the Value=Now (to show when it was completed) This would probably be more simple.

    I was already trying to figure out how to add the timestamp to the cell when the check box was checked. LOL

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

    Default Re: Insert checkbox using button

    So tell me. When you double click on what cell?
    You want that cells background color to be what color say like red blue green yellow
    And enter todays date in what cell.


    I need you to say something like this:

    When I double click on any cell in column B to turn that cells background color to yellow and put todays date in column A

    Do not say when I double click on column address.
    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
  •