Related:
- Excel - enter current time in a cell by clicking it
- Which function is used to enter current time in a worksheet cell - Best answers
- Entering current time in specific cells ✓ - Forum - Excel
- Excel - Insert current date or time - How-To - Hardware
- Excel - Enter text in bold for a particular cell - How-To - Excel
- Excel repeat a text in different cells specifying number of times to repeat - How-To - Excel
- How to enter multiple lines in one cell in excel - How-To - Excel
7 replies
ChrisS
Check this one out. It worked great for me! If anything you would just have to have a blank cell with something like:
Assuming A2 as the input cell:
Type this in D2
=if(A2=1,"Dispatched","")
Then have your guys just type a "1" when they dispatch.
Then right click on the spreadsheet tab. Select "View Code"
Copy and paste this:
--------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
With Target(1, 3)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub
---------------------------------------------------- (without the lines though).
Every time they type a 1 on A2 and hit "Enter" the date will appear on B2, time on C2 and "Dispatched" on D2.
Hope this helps.
Assuming A2 as the input cell:
Type this in D2
=if(A2=1,"Dispatched","")
Then have your guys just type a "1" when they dispatch.
Then right click on the spreadsheet tab. Select "View Code"
Copy and paste this:
--------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then
With Target(1, 3)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub
---------------------------------------------------- (without the lines though).
Every time they type a 1 on A2 and hit "Enter" the date will appear on B2, time on C2 and "Dispatched" on D2.
Hope this helps.
Helper
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.
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.
Helper
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.
that has to be modified this click event will erase what might be in the current cell.
Emmettken
>
WutUp WutUp
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
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
Tenguzake
>
Helper
This has been a great help for me, I need the day of week, date and time for other calculations so I modified the code to read (see below). My problem is I need them in different columns. C2:C2000 mm/dd/yyyy, D2:D2000 dow "dddd" and E2:E2000 time "ttttt". If the user has to single click in each column they won't mind - becuase right now they have to type it. Can you help me?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("C3:C2000")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
Target = Format(Now, "mm/dd/yyyy, dddd, ttttt")
End If
SkipIt:
Exit Sub
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("C3:C2000")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
Target = Format(Now, "mm/dd/yyyy, dddd, ttttt")
End If
SkipIt:
Exit Sub
End Sub
bhargav
for diff columns just the time
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("C:F")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
Target = Format(Now, "ttttt")
End If
SkipIt:
Exit Sub
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("C:F")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub
Else
Target = Format(Now, "ttttt")
End If
SkipIt:
Exit Sub
End Sub
Sami
Hi Fellow,
Dear ChrisS ..
I've tried your macro & it was great.
Anyhow, How can I copy the same macro to become when I type 1 in F2 then the Date will appear on G2 & the Date on H2.
In the end I want that when I type in the A2 the InTime& Date appear in B2 & F2.
And when I type in the F2 the OutTime & Date Will appear in G2&H2
Dear ChrisS ..
I've tried your macro & it was great.
Anyhow, How can I copy the same macro to become when I type 1 in F2 then the Date will appear on G2 & the Date on H2.
In the end I want that when I type in the A2 the InTime& Date appear in B2 & F2.
And when I type in the F2 the OutTime & Date Will appear in G2&H2
rizvisa1
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
Are you sure you want to change B and F col, if A changes. The reason is that you have if if F changes then have G and H changes
Any how, here is the modified code. I have put comments in the code so you should be able to expand the code if needed or modify the code if needed
Any how, here is the modified code. I have put comments in the code so you should be able to expand the code if needed or modify the code if needed
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim sDateCol As String
Dim sTimeCol As String
'if the cell(s) changed was not in col A or Col F then nothing to do
If Intersect(Target, Union(Range("A2:A" & Rows.Count), Range("F2:F" & Rows.Count))) Is Nothing Then GoTo End_Sub
' disable event
Application.EnableEvents = False
On Error GoTo Error_Handler
' for every modified cell
For Each Cell In Target
' if the modified cell is not in col 1 (A) or at col 6 (F) then go to next changed cell
If ((Cell.Column <> 1) And (Cell.Column <> 6)) Then GoTo Next_Cell
' if changed cell is at col 1 (col A)
If (Cell.Column = 1) Then
sDateCol = "B"
sTimeCol = "F"
Else ' default position that changed cell is at col 6 (col F)
sDateCol = "G"
sTimeCol = "H"
End If
' same row but date col
With Cells(Cell.Row, sDateCol)
.Value = Date
.EntireColumn.AutoFit
End With
' same row but time col
With Cells(Cell.Row, sTimeCol)
.Value = Time
.EntireColumn.AutoFit
End With
Next_Cell:
Next Cell
End_Sub:
Application.EnableEvents = True
Exit Sub
Error_Handler:
MsgBox Err.Description
GoTo End_Sub
End Sub
Subscribe To Our Newsletter!
Can you please show me, if I am using date in C2 and frequency in D2(in months variable) and like to update in E2
If Not Intersect(Target:range.... the first one
It complains at Intersect