is it possible to CLICK & insert date

  • Thread starter Thread starter MIKE
  • Start date Start date
M

MIKE

is it possible to
flag a column as a date column
and when you click a cell the date AND time is
inserted ???
 
Mike,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = Date
End If
End Sub

Insert this code in the appropriate worksheet module. Change the column
number for your target column. It will insert the date just by entering the
cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Mike
Change "Date" to "Now" to get the date and time. Format the cells
accordingly. HTH Otto
 
Unfortunately, this will also change the date of previously date-stamped
cells if the user navigates to them using the keyboard. I think the OP would
be better advised to use the DoubleClick event; that way there's no
confusion:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As _
Range, Cancel As Boolean)
Cancel = True
If Target.Column = 1 Then Target.Value = Now
End Sub

--

Vasant
 
Vasant,

You are probably right, but personally I do dislike the Double-Click event
<G>.

You could always test for the target being empty.

Bob
 
Hi Bob:

Matter of taste, I guess! I always try to discourage the use of the
SelectionChange as a substitute for the nonexistent Worksheet_Click event
because it makes it so much easier for the Law of Unintended (and possibly
Unnoticed) Consequences to rear its ugly head <g>. People click all over a
worksheet without much conscious thought; double-clicking on a cell implies
true intent.

Also, I can just see an inexperienced user opening the sheet with the cursor
on A1 and scrolling down hundreds of rows with the SelectionChange event
firing and changing dates every 40 rows or so!

It would be nice if the SelectionChange event had a parameter to determine
whether the navigation was done via mouse or keyboard.

Regards,

Vasant.
 
Hi Vasant,

You hit the nail on the head there - the lack of the Worksheet_Click event
is the real problem. Must keep pressing MS for such.

Just a thought, if there were such an event, would SelectionChange then
just be triggered by keyboard, or would two events fire in the event of
clicking a cell?

Regards

Bob
 
Good question, Bob ... I can see problems with either implementation. That's
why I think having a single Selection_Change event with an option to
determine the method of navigation would give us the best of both worlds.
Then you could stop the event from firing (if you so wished) if the
navingation was via keyboard.

But I have a feeling we're going to be stuck with the current implelentation
for the foreseeable future ...

Regards,

Vasant.
 
I must admit, some sort of indication as to what fired the SeelectionChange
event , aka the CloseMode on a form QueryClose event, was not something that
occurred to me. And I think you are right about any changes.

Anyway, we must soldier on.

Regards

Bob
 
hi thanks for the code
but what can i add to it to NOT ONLY insert the Date
but the date and the time ?

thanks

MIKE KNOPFLER
COMPLEX PLASTICS LLC.
 
Back
Top