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]

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.

When i double click on any cell on columns G, H, or I, turn that cell blue and insert todays date and time in to that same cell.

Like that?

 

[My Aswer Is This]

Try this:
OK. If some one double clicks on column G H or I this script will run.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 4-11-18 8:35 PM EDT
If Not Intersect(Target, Range("G:I")) Is Nothing Then
Cancel = True
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cells(Target.Row, Target.Column).Value = Date
Cells(Target.Row, Target.Column).Interior.Color = vbBlue
End If
End Sub

 

[My Aswer Is This]

Now this may not be what you want because I asked what cell and you said G H Or I

Well are all three of these cells now empty?
Because if you double click on G and G already has data the date is now going to over write the data in the cell.

And I'm surprise would want to see a date in column G on one row and then see a date in column H on another row and then maybe a date in another column of another row

I would think you would say G

So are trying to say I should write the code and make the choice of which one or you mean to allow the user to decide which one.

The way I wrote this code is that if the cell is already filled in then over write what's in the cell.

 

[harveya915]

My apologies, been out and busy

Yes, the cells (G, H, I) will be empty. I did some tinkering and got it to work, I think. I removed one line of code:

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

This is my final result, I moved G:I to I:K

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'DOUBLE CLICK CELL TO INSERT DATE AND COLOR
'Modified 4-11-18 8:35 PM EDT
Worksheets("Sheet1").Unprotect Password:="1760"
If Not Intersect(Target, Range("I:K")) Is Nothing Then
Cancel = True
Cells(Target.Row, Target.Column).Value = Date
Cells(Target.Row, Target.Column).Interior.Color = RGB(153, 204, 255)
Cells(Target.Row, Target.Column).Font.Color = vbBlack
Cells(Target.Row, Target.Column).Font.Size = 8
Worksheets("Sheet1").Protect Password:="1760"
End If
End Sub

Let me know if I could have gone a different route.

I really appreicate all your help