Automatic Dates

[Orf Bartrop]

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

 

[Max]

Try: Ctrl + ;

 

[Bob Phillips]

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]

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]

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]

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


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]

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]

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]

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]

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

Orf

 

[Bob Phillips]

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)