Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am authoring a simple spreadsheet. I want to have it so that If column A
has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm sorry if I didn't explain before but, I'm not real good at this. I almost
get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
No, that isn't feasible.
The instructions were all included with the code Jim, just give it a shot. BTW, my keyboard is playing up so there were @ where there should have been ". Try this version Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,"dd mmm yyyy") 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" wrote in message ... I'm sorry if I didn't explain before but, I'm not real good at this. I almost get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Select the worksheet that should have this behavior.
Rightclick on that worksheet tab. Select View Code Paste this modified version of Bob's code into the newly opened code window (to the right): Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target.offset(0,5) .Value = date .numberformat = "dd mmm yyyy" End With End If ws_exit: Application.EnableEvents = True End Sub Then back to excel and type something in column A. If nothing happens to column F for that row, then close and save the workbook. And then reopen it, but allow macros to run. Jim Savage wrote: I'm sorry if I didn't explain before but, I'm not real good at this. I almost get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Dave and Bob,
You guys are great. That worked like a charm. I didn't even know about that type of code. I am indebted to you both for showing me the way ( Buddist Chant OUWMMMMMMMM) Thanks again JIM "Dave Peterson" wrote: Select the worksheet that should have this behavior. Rightclick on that worksheet tab. Select View Code Paste this modified version of Bob's code into the newly opened code window (to the right): Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target.offset(0,5) .Value = date .numberformat = "dd mmm yyyy" End With End If ws_exit: Application.EnableEvents = True End Sub Then back to excel and type something in column A. If nothing happens to column F for that row, then close and save the workbook. And then reopen it, but allow macros to run. Jim Savage wrote: I'm sorry if I didn't explain before but, I'm not real good at this. I almost get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? -- Dave Peterson |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
The only chant I know was taught to me in stages:
Ow -- Wa -- Ta -- Goo -- Si -- Am It sounds better the faster you say it. Jim Savage wrote: Dave and Bob, You guys are great. That worked like a charm. I didn't even know about that type of code. I am indebted to you both for showing me the way ( Buddist Chant OUWMMMMMMMM) Thanks again JIM "Dave Peterson" wrote: Select the worksheet that should have this behavior. Rightclick on that worksheet tab. Select View Code Paste this modified version of Bob's code into the newly opened code window (to the right): Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target.offset(0,5) .Value = date .numberformat = "dd mmm yyyy" End With End If ws_exit: Application.EnableEvents = True End Sub Then back to excel and type something in column A. If nothing happens to column F for that row, then close and save the workbook. And then reopen it, but allow macros to run. Jim Savage wrote: I'm sorry if I didn't explain before but, I'm not real good at this. I almost get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? -- Dave Peterson -- Dave Peterson |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
The code was great, however, is there way to have the date modified appear in
one cell only so that when a workbook is opened you can see instantly the last time the sheet was saved. The cell would then be overwritten with the next date modified when the workbook is opened again? "Dave Peterson" wrote: The only chant I know was taught to me in stages: Ow -- Wa -- Ta -- Goo -- Si -- Am It sounds better the faster you say it. Jim Savage wrote: Dave and Bob, You guys are great. That worked like a charm. I didn't even know about that type of code. I am indebted to you both for showing me the way ( Buddist Chant OUWMMMMMMMM) Thanks again JIM "Dave Peterson" wrote: Select the worksheet that should have this behavior. Rightclick on that worksheet tab. Select View Code Paste this modified version of Bob's code into the newly opened code window (to the right): Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target.offset(0,5) .Value = date .numberformat = "dd mmm yyyy" End With End If ws_exit: Application.EnableEvents = True End Sub Then back to excel and type something in column A. If nothing happens to column F for that row, then close and save the workbook. And then reopen it, but allow macros to run. Jim Savage wrote: I'm sorry if I didn't explain before but, I'm not real good at this. I almost get it. Can you write this in more of a formula format? Sorry to be a pain but, Spreadsheets aren't my specialty by any means. I try to stick to CAD. Thanks "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0,5).Value = format(date,@dd mmm yyyy@) 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 -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jim Savage" <Jim wrote in message ... I am authoring a simple spreadsheet. I want to have it so that If column A has any value entered what so ever that column f will display the date that the entry was made. Is this possible? Any Suggestions? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Date Cut & Paste Issue | Excel Discussion (Misc queries) | |||
Excel 2003: date display issue | Excel Discussion (Misc queries) | |||
Date format issue | Excel Worksheet Functions | |||
Another Date issue. | Excel Worksheet Functions | |||
Date conversion issue "^" symbol | Excel Discussion (Misc queries) |