Insert checkbox using button

[harveya915]

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!

 

[harveya915]

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!

 

[harveya915]

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.

 

[harveya915]

Try...

[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]

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.

 

[My Aswer Is This]

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?
​

<strike></strike>
​

 

[Domenic]

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.

 

[harveya915]

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?
​

<strike></strike>
​

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.

 

[My Aswer Is This]

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.

 

[My Aswer Is This]

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.

 

[harveya915]

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

 

[My Aswer Is This]

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.