Search : in
By :

Enter current time in a cell by clicking it

Last answer on Aug 24, 2009 3:24:23 pm BST ncturtle, on Feb 8, 2009 6:51:29 pm GMT 
 Report this message to moderators

Hello,

I'm trying to minimize the effort to input data in a sheet I've built for recording time for dispatched workers I need to find a way to fill a cell with the current time by just clicking or double clicking on it. The idea being that a technician can see the details of a work order, then just click on the appropriate cell to record their dispatch time.

I'm a noob to functions and VB but I'm familiar with Excel, I need to know where to start. Thanks

I'm using office 2007, if that makes any difference.

Configuration: Windows Vista
Firefox 3.0.6

Best answers for « Enter current time in a cell by clicking it » in :
Excel tips : How to insert date in a cell Show Excel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
Insert advertising on your blog ShowInsert advertising on your blog Advertising is one of the main revenue sources for websites. However nowadays it is possible to make some money by inserting advertisings (banners, sponsored links) on your blog. Modules for e...
[Excel]Storage name and path of a spreadsheet Show[ExcelStorage name and path of a spreadsheet The tip below will show you how to automatically show the storage path and the name of any spreadsheet Enter in a cell (E.g cellA1) the following formula: =cell("filename", A1)...
Change your Yahoo password ShowChange your Yahoo password Go to Yahoo Click on Sign in Enter your email address and your password and click Login. Place your cursor on "hi, user_name" Click account info Re-enter your password and click Login. In the Sign-In...
Download OutWit Hub ShowTo get information on the web it is sometimes necessary to lose time and click, copy and past. OutWit Hub is a collection engine of Web. It will allow you to find what you look for on the Web with a very few clicks without viewing lines of text...
Download Easy Time Control Free ShowIf you possess a small firm and what you must manage several employees, particularly their arrival and exit times, use Easy Time Control Free. Easy Time Control Free is a tool allowing to manage the hour of entrance and exit hours of several...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Hyperlinks ShowIntroduction to anchors Hypertext links or hyperlinks (anchors) are HTML elements that, when clicked on, enable readers to visit a new address. Hyperlinked text is underlined by default. Hyperlinks are what connect web pages to one another. They...

1

Helper, on Feb 9, 2009 1:53:29 am GMT
  • +1

I think this is what you are looking for, but let me explain where the code must reside.
Since you want the time to happen by a click event you must put the code in the
Worksheet SelectionChange event in the worksheet itself and not just create a macro.
You stated you are using Office 2007 so here is what you do.

1) Click on the Developer Tab.
2) Click on the Visual Basic icon at the top left of the ribbon.
3) Now on the left pane window, you will see a folder called Microsoft Excel Objects that should be expanded already with the worksheet names of your workbook.
4) Double click on the worksheet where you want the time to appear.
5) Now you will see two drop down boxes; on the left you will see (General), and on the right you will see (Declarations).
6) Click on the left drop down and change (General) to Worksheet.
7) Now you will see Private Sub Worksheet_SelectionChange(ByVal Target As Range)
8) This is the event you need for the mouse click. You will only have to click the mouse once.
9) Copy and paste this code between Private Sub Worksheet_SelectionChange(ByVal Target As Range)
and End Sub.

If Target.Address = ActiveCell.Address Then

Target = Format(Now, "ttttt")

End If

10) Close the Visual Basic Editor and give it a try.

Reply to Helper

2

Helper, on Feb 9, 2009 2:08:46 am GMT
  • +1

I forgot to mention. The code works for any cell that is clicked on the worksheet so if you have other info.
that has to be modified this click event will erase what might be in the current cell.

Reply to Helper

3

Helper, on Feb 9, 2009 2:53:19 am GMT

Just to clarify. If there are only certain cells that need to be clicked, then something like this will work better.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case Range("A1").Address

Target = Format(Now, "ttttt")

Case Range("A5").Address

Target = Format(Now, "ttttt")

Case Range("A10").Address

Target = Format(Now, "ttttt")

End Select


End Sub

Reply to Helper

4

Seek, on Mar 13, 2009 5:51:52 pm GMT

I just used your suggestion for getting times in certain cells and it worked great. Now lets say intead of just cell F4, I want to include F4-F100? How would I change the code. And is there a way to lock them once they are entered? Thank you very much!

Reply to Seek

5

WutUp WutUp, on Mar 13, 2009 10:27:26 pm GMT

As you can see, I changed my Nickname. Anyway, let's get to business.

The code below will give you the range F4:F100 as you requested and the sheet protection.
What you need to know also is that everytime you must unprotect the sheet when selecting the range F4:F100. Do this by clicking on the
Review tab and then click on the Unprotect Sheet icon. This will give you access to the range F4:F100
that you want to change. Now, if you immediately click anywhere in the range F4:F100 again, you will see that nothing happens. The reason is that once the code runs it will protect the cells so you don't accidentally click on
another cell. Just unprotect the sheet again in the Review tab.

Hope this helps!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range
Dim IntersectRange As Range

Set MyRange = Range("F4:F100")

Set IntersectRange = Intersect(Target, MyRange)

On Error GoTo SkipIt

If IntersectRange Is Nothing Then
Exit Sub

Else

Target = Format(Now, "ttttt")

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xllockedCells

End If

SkipIt:
Exit Sub

ActiveSheet.Unprotect
Rows("1:3").Select
Range("1:3,A4:E65536").Select
Range("A4").Activate
Range("1:3,A4:E65536,G4:IV65536").Select
Range("G4").Activate
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False

End Sub

Reply to WutUp WutUp

6

BradCustom, on Apr 6, 2009 9:35:38 pm BST
  • +3

I've tried your formula and it works great with one exception; when you try to sort all the fields change to the current date and time. Can the code be changed to a double click on the cell instead of a single click.

Thanks

Reply to BradCustom

7

WutUp WutUp, on Apr 7, 2009 12:24:33 am BST
  • +1

You will need to put the code in the Worksheet Before Double click AppEvent instead of the Selection
Change event. You will probably notice it takes slightly longer for the code to run when you double click.


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

Dim MyRange As Range
Dim IntersectRange As Range

Set MyRange = Range("F4:F100")

Set IntersectRange = Intersect(Target, MyRange)

On Error GoTo SkipIt

If IntersectRange Is Nothing Then
Exit Sub

Else

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Target = Format(Now, "ttttt")

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xllockedCells

End If

ActiveSheet.Unprotect
Rows("1:3").Select
Range("1:3,A4:E65536").Select
Range("1:3,A4:E65536,G4:IV65536").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False

ActiveCell.Offset(, 1).Select

SkipIt:
Exit Sub


End Sub

Reply to WutUp WutUp

9

mia, on Jul 2, 2009 7:38:02 am BST
  • +3

Can I use this code in excel 2003?

Reply to mia

10

 Emmettken, on Aug 24, 2009 3:24:23 pm BST

I tried the first part of your formula and it worked great. When I tried to modify it so I had to double click a cell and the cell had to be in a pre defined area, it went bad.

Would you please show mw a formula that double clicks the current time in a pre-defined area of cells.

I really appreciate this. At one time I was fairly good with Excel (Lotus prior) and then 15 years of not using it have erased my mind.

Thanks Ken

Reply to Emmettken

8

arunsakthivel, on Jun 23, 2009 9:14:48 pm BST

Awsome!!!!!!!!!!!!! ;-)

Reply to arunsakthivel