Insert checkbox using button

harveya915

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

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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.
 
Last edited:

harveya915

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

Code:
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
This is my final result, I moved G:I to I:K

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,027
Messages
5,484,266
Members
407,436
Latest member
Szafranski

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top