Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Change Changing Date Format Data to Text | Excel Discussion (Misc queries) | |||
how do i change text format date to date (i.e., mm/yy to mm/dd/yyy | Excel Discussion (Misc queries) | |||
I need a date stamp that doesn't change when you enter text | Excel Worksheet Functions | |||
CHANGE TEXT TO DATE FORMAT | Excel Discussion (Misc queries) | |||
Change Text Data to Date | Excel Discussion (Misc queries) |