Change text to date
 

I want to be able to type "X" into a cell, and when I hit enter I want it to
change to today's date. I can not do

Change text to date
 

One way:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Value = "X" Then _
Target.Value = Date
End Sub
This macro will place today's date in any cell in the entire sheet if "X" is
entered into that cell. Post back if you want to restrict the action to a
specific range.
This macro is a sheet event macro and must be placed in the sheet module of
your sheet. To access that module, right-click on the sheet tab, select
View Code, and paste this macro into that module. "X" out of the module to
return to your sheet. HTH Otto
"Chey" wrote in message
...
I want to be able to type "X" into a cell, and when I hit enter I want it
to
change to today's date. I can not do

Change text to date
 

Right click on the sheet tab, and choose View Code. The VB Editor opens,
with a code window that shows the workbook name then a sheet name like
"Sheet1" (which may or may not match the name on the sheet tab) at the top.
Copy this code and paste it into the code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase$(Target.Value) = "X" Then
Target.Value = Format(Now, "mm/dd/yy")
End If
End Sub

This works whenever a cell in that particular sheet is changed. If it is
changed to x or X, the cell is filled with today's date, otherwise there is
no change.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Chey" wrote in message
...
I want to be able to type "X" into a cell, and when I hit enter I want it
to
change to today's date. I can not do

Change text to date
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If UCase(.Value) = "X" Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chey" wrote in message
...
I want to be able to type "X" into a cell, and when I hit enter I want it
to
change to today's date. I can not do

Change text to date
 

How about using excel's built-in shortcut:
ctrl-;
(ctrl-semicolon)

(I think it would be quicker.)

ctrl-: (ctrl-colon) will enter the time

Chey wrote:

I want to be able to type "X" into a cell, and when I hit enter I want it to
change to today's date. I can not do

--

Dave Peterson