Automatic Dates
 

Is there a way of automatically entering today's date so that it does
not change when the program is opened on a later date. I tried the
TODAY() command but that updates the date to the current date.

Orf Bartrop

Automatic Dates
 

Try: Ctrl + ;

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <atyahoo<dotcom
----
"Orf Bartrop" wrote in message
...
Is there a way of automatically entering today's date so that it does
not change when the program is opened on a later date. I tried the
TODAY() command but that updates the date to the current date.

Orf Bartrop

Automatic Dates
 

Hi Orf,

VBA will do it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, so right-click the sheet tab, select View
Code, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Orf Bartrop" wrote in message
...
Is there a way of automatically entering today's date so that it does
not change when the program is opened on a later date. I tried the
TODAY() command but that updates the date to the current date.

Orf Bartrop

Automatic Dates
 

Thanks, Bob. I did as you directed and came up with an error:
Compile error: Ambiguous name detected: Worksheet-Change

I think this is happening because there is already a statement:
Private Sub Worksheet_Change(ByVal Target As Range
followed by End Sub (nothing between).

I tried deleting those two statements but then data entered in the sheet
was not correct.

I do not know what wording to change to get it to work.

Orf

Bob Phillips wrote:

Hi Orf,

VBA will do it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, so right-click the sheet tab, select View
Code, and paste the code in.

Automatic Dates
 

Orf,

Your reasoning is correct.

Post the 2 change event codes and I will try and merge them for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Orf Bartrop" wrote in message
...
Thanks, Bob. I did as you directed and came up with an error:
Compile error: Ambiguous name detected: Worksheet-Change

I think this is happening because there is already a statement:
Private Sub Worksheet_Change(ByVal Target As Range
followed by End Sub (nothing between).

I tried deleting those two statements but then data entered in the sheet
was not correct.

I do not know what wording to change to get it to work.

Orf

Bob Phillips wrote:

Hi Orf,

VBA will do it

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, so right-click the sheet tab, select View
Code, and paste the code in.

Automatic Dates
 

Bob, the codes that were present before I added your code a


Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


I then added your code:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



and that is when an error message came up.

Orf



Bob Phillips wrote:

Orf,

Your reasoning is correct.

Post the 2 change event codes and I will try and merge them for you.

Automatic Dates
 

Hey Orf,

That is so easy.

The other two do nothing, so just get rid of them.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Orf Bartrop" wrote in message
...
Bob, the codes that were present before I added your code a


Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub



I then added your code:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = Format(Date, "dd mmm yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



and that is when an error message came up.

Orf



Bob Phillips wrote:

Orf,

Your reasoning is correct.

Post the 2 change event codes and I will try and merge them for you.

Automatic Dates
 

Thanks Bob, as you can see I know little about coding. I have done as
you suggested and so far so good. I will wait until tomorrow to see if
it works and retains the given date.

Orf

Bob Phillips wrote:

Hey Orf,

That is so easy.

The other two do nothing, so just get rid of them.

Automatic Dates
 

It will be okay - have faith.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Orf Bartrop" wrote in message
...
Thanks Bob, as you can see I know little about coding. I have done as
you suggested and so far so good. I will wait until tomorrow to see if
it works and retains the given date.

Orf

Bob Phillips wrote:

Hey Orf,

That is so easy.

The other two do nothing, so just get rid of them.

Automatic Dates
 

Whoops. That change caused the date to come up in every cell in all
columns and rows.

Orf

Bob Phillips wrote:

It will be okay - have faith.

Automatic Dates
 

Nonsense<vbg

Just tried it, it works fine.

Send me your workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Orf Bartrop" wrote in message
...
Whoops. That change caused the date to come up in every cell in all
columns and rows.

Orf

Bob Phillips wrote:

It will be okay - have faith.