#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Date issue

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Date issue

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Cut & Paste Issue Sweepea Excel Discussion (Misc queries) 8 October 28th 09 08:20 PM
Excel 2003: date display issue Andrew Excel Discussion (Misc queries) 17 July 13th 06 06:36 PM
Date format issue LadyDoe Excel Worksheet Functions 1 November 17th 05 08:54 AM
Another Date issue. TimM Excel Worksheet Functions 1 November 17th 05 02:58 AM
Date conversion issue "^" symbol Shannon Excel Discussion (Misc queries) 1 May 4th 05 01:25 AM


All times are GMT +1. The time now is 04:12 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"