Add date to cell with a right mouse click

R

rirus

New Member
Joined
Dec 8, 2006
Messages
46
I want to select a function from a right mouse click that will place the current date in the selected cell.

Example:

1) Click on a cell
2) Perform a right mouse click
3) select the date function
4) the current date appears in selected cell

could this be set up so that the date function in the right mouse click only appears when specified cells are selected? Is this doable?


Rirus
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi Rirus,

The following BeforeRightClick worksheet event should do the job. To install it follow these four steps:

1. Copy (Ctrl + C) my code to the clipboard
2. Right click the sheet tab you wish the code to run on and from the shortcut menu select View Code
3. Paste (Ctrl + V) my code from step one into the blank module
4. From the File menu select Close and Return to Microsoft Excel

Bear in mind that every time a user right clicks today's date will be inserted into the activecell which may cause some frustration if they are just trying to use the worksheet's shortcut menu.

HTH

Robert

Code: 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Dim dteTodaysDate As Date
    
    dteTodaysDate = Format(Now(), "dd/mm/yyyy")

    ActiveCell.Value = dteTodaysDate

End Sub
 
Upvote 0
Trebor76 said:
Bear in mind that every time a user right clicks today's date will be inserted into the activecell which may cause some frustration if they are just trying to use the worksheet's shortcut menu.

Code: 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Dim dteTodaysDate As Date
    
    dteTodaysDate = Format(Now(), "dd/mm/yyyy")

    ActiveCell.Value = dteTodaysDate

End Sub
Click to expand...

Rirus-
You mentioned having this apply only to specific cells. To accomplish that, take Robert's code above, and wrap it in the following:

Code: 
    Dim rng As Range
    Set rng = Range("A1:B2")    'this range can be whatever range you wish
    
    If Not Intersect(Target, rng) Is Nothing Then
              ' Robert's date code.
    End If
That way, the date "stamp" will only be applied if the selected cell intersects with the range of cells you want to affect. So the full code will look like:

Code: 
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    ' Variables.
    Dim rng As Range
    Dim dteTodaysDate As Date

    ' Set the range you want this apply to
    Set rng = Range("A1:B20")    
    
    ' Check to see if the to ranges intersect...
    If Not Intersect(Target, rng) Is Nothing Then
         ' ...and if they do, apply the date.
         dteTodaysDate = Format(Now(), "dd/mm/yyyy")
         ActiveCell.Value = dteTodaysDate
    End If

End Sub
 
Upvote 0
I would use the BeforeDoubleClick event instead. Numerous examples on the board

lenze
 
Upvote 0
Awesome... works beautifully. Used the BeforeDoubleClick and it worked much better as I did not get the dropdown... it was very distracting.

Another related Question:
Could the date function be put into the actual dropdown and selected there? If it could then I would be able to add a time function as well. It would be nice if the selected cells could be specified for when a date or a time function would be selectable.

Examlple:
When a specified Cell is selected and a right mouse click is performed, the dropdown occurs and displays a current date function if the selected cell is in columns B, F or I. If a selected cell is in Columns C, G or J a current time function would be selectable in the dropdown.

The date and times are in seperate columns otherwise I could have formatted the Now() accordingly, combining the date and time together.

Thanks for all your help on this... it will make my life a lot easier.
 
Upvote 0
I modifed Scott's code to get the date and time function working on a BeforeDoubleClick when specified cells are selected... works great.

Code: 
 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' Variables.
    Dim rng1, rng2 As Range
    Dim dteTodaysDate, dteCurrentTime  As Date

    ' Set the range you want this apply to
    Set rng1 = Range("B:B")
    Set rng2 = Range("A:A")
    ' Check to see if the to ranges intersect...
    If Not Intersect(Target, rng2) Is Nothing Then
         ' ...and if they do, apply the date.
         dteTodaysDate = Format(Now(), "dd/mm/yyyy")
         ActiveCell.Value = dteTodaysDate
    ElseIf Not Intersect(Target, rng1) Is Nothing Then
        ' ...and if they do, apply the Time.
         dteCurrentTime = Format(Time(), "hh:nn:ss")
         ActiveCell.Value = dteCurrentTime
    End If

End Sub

Also,
I was looking at some of the other post for the BeforeRightClick and found enough info to figure out how to add the Date and Time functions to the right mouse click dropdown.

Thanks,

lenzy - for the BeforeDoubleClick and recommending the other post &
Scott and Trebor76 - for the code.

It was a big help.

rirus
 
Upvote 0
One further note for the BeforeDoubleClick. You should include the line
Code: 
Cancel=True
This will stop Excel from going into Edit mode, which is the default action of a doubleclick. BTW, the same code in the BeforRightClick event will prevent the dropdown.

lenze
 
Upvote 0
This sounds like what I am looking to do. Two Q's: What do I physically do after putting the code in the worksheet to get the date/time to appear and where do I add the Cancel=True line? Tried double-clicking in the empty cell, didn't work. Thanks in advance.
 
Upvote 0
I placed the code, Worksheet_BeforeDoubleClick from the previous post into WorkSheet1 in a new Workbook.

1) Open new workbook
2) launch the Visual Basic (VB) Editor
3) In the VB Editor, Double Click on Sheet1 (Sheet1) in the Project Explorer Window

If needed...
Note: To open Project Explorer Window in the VB Editor, select View and Click on Project Explorer

4) Copy the code from the previous post in this string and paste it into the Worksheet (right side window) in the VB Editor.
5) save the workbook

Open worksheet1 and DoubleClick a cell in Column A and then in Column B . The current date should appear in Column A and the Current time in Column B. All other Columns are not affected.

If you want to use the Right Mouse Click change Worksheet_BeforeDoubleClick to Worksheet_BeforeRightClick and place the Cancel = True under the line...

Dim dteTodaysDate, dteCurrentTime As Date
Cancel = True

Now, when you do a right Mouse Click in Columns A or B the same thing will happen as with the Worksheet_BeforeDoubleClick code.

Note: If it doesn't work the first time you try it, remove the code, save the workbook and then paste the code back in... re-save.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Choose from List Box
Replies
5
Views
154
ddnron
D
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
344
Skyybot
Skyybot
K
  • Question Question
Stocktake of Barcoded Inventory in Excel using a Barcode Scanner
Replies
9
Views
290
Kobus Horn
K
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
204
datatronics505
datatronics505
D
  • Question Question
Copy cell $G$1 (value only) 1 cell to the right of the checkbox when clicked
Replies
1
Views
173
Artik
Artik

Forum statistics

Threads
1,224,507
Messages
6,179,176
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top