Static Now() function

[sike11 via OfficeKB.com]

I saw this code in an earlier posting:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
Application.EnableEvents = False
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("A" & n).Value = Now
End If
End If

enditall:
Application.EnableEvents = True
End Sub

I have made adjustments so that it can display the date and time in the
column I want. However,
I get an error which says "Argument not optional".

Please help. Also, would it be possible to adjust it so that it works across
multiple worksheets?

Thank you in advance.

This is sheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste into that module.

Macros must be enabled by the user when opening the workbook for this code to
be
available.

 

[Guest]

Worksheet_change only works on one worksheet. You need to copy this code
into each worksheet page you are going to use it for. when you view code,
you will see a project window on the left showing all the worksheets. You
havve to copy the code into each worksheet. double click on each sheet will
bring up a new VBA window where you can place the code.

 

[Dave Peterson]

All the worksheets in the workbook follow the same rule--a change made in column
B means that the date/time will be inserted into column A.

If yes, then you can put this code behind the ThisWorkbook module (not any
particular sheet).

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only column B
If Intersect(.Cells, Sh.Range("b:b")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
On Error GoTo EndItAll

If .Value = "" Then
'do nothing
Else
Sh.Cells(.Row, "A").Value = Now
End If
End With

EndItAll:
Application.EnableEvents = True

End Sub

And remove any worksheet_change event that you've added that does the same
stuff.

 

[sike11 via OfficeKB.com]

Hi Dave,

Thanks for responding so quickly....much appreciated. Some minor adjustments
made to the columns and the code now looks like:

Option Explicit
Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only column E
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
End If

Application.EnableEvents = False
On Error GoTo EndItAll

If .Value = "" Then
'do nothing
Else
Sh.Cells(.Row, "F").Value = Now
End If
End With

EndItAll:
Application.EnableEvents = True

End Sub

I then put the code in the "ThisWorkbook" module as you suggested. However, I
am not quite sure how to get it to run. Please bear with me as I am a novice
to writing VBA. Any ideas?

I will be forever grateful for any help.

Regards
Mary.

All the worksheets in the workbook follow the same rule--a change made in column
B means that the date/time will be inserted into column A.

If yes, then you can put this code behind the ThisWorkbook module (not any
particular sheet).

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only column B
If Intersect(.Cells, Sh.Range("b:b")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
On Error GoTo EndItAll

If .Value = "" Then
'do nothing
Else
Sh.Cells(.Row, "A").Value = Now
End If
End With

EndItAll:
Application.EnableEvents = True

End Sub

And remove any worksheet_change event that you've added that does the same
stuff.

I saw this code in an earlier posting:

[quoted text clipped - 34 lines]

 

[Dave Peterson]

First, you'll have to fix a typo:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
End If

Becomes this:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then
Exit Sub
End If

or this:
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub

But not both!

=====
And it should run automatically (if macros are enabled and events are also
enabled) when you type anything into any cell in any worksheet in that workbook.

It should exit the subroutine if your change is to multiple cells--or if your
change isn't in column E.

Hi Dave,

Thanks for responding so quickly....much appreciated. Some minor adjustments
made to the columns and the code now looks like:

Option Explicit
Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only column E
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
End If

Application.EnableEvents = False
On Error GoTo EndItAll

If .Value = "" Then
'do nothing
Else
Sh.Cells(.Row, "F").Value = Now
End If
End With

EndItAll:
Application.EnableEvents = True

End Sub

I then put the code in the "ThisWorkbook" module as you suggested. However, I
am not quite sure how to get it to run. Please bear with me as I am a novice
to writing VBA. Any ideas?

I will be forever grateful for any help.

Regards
Mary.

All the worksheets in the workbook follow the same rule--a change made in column
B means that the date/time will be inserted into column A.

If yes, then you can put this code behind the ThisWorkbook module (not any
particular sheet).

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target
'one cell at a time
If .Cells.Count > 1 Then Exit Sub

'only column B
If Intersect(.Cells, Sh.Range("b:b")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
On Error GoTo EndItAll

If .Value = "" Then
'do nothing
Else
Sh.Cells(.Row, "A").Value = Now
End If
End With

EndItAll:
Application.EnableEvents = True

End Sub

And remove any worksheet_change event that you've added that does the same
stuff.

I saw this code in an earlier posting:

[quoted text clipped - 34 lines]

 

[sike11 via OfficeKB.com]

Hi Dave,

Thanks for this. Typo has been fixed. I just have another question. The first
cell i.e "E1" has a title in it. So the next cell the event should affect is
"E2". How will I adjust the code to reflect this.

What happens is that a bar code reader reads the info but I need a date/time
to show when the info was read. When it is next used, the date and time of
the previous cell should not change.

Any help gratefully accepted.

Mary.

First, you'll have to fix a typo:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
End If

Becomes this:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then
Exit Sub
End If

or this:
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub

But not both!

=====
And it should run automatically (if macros are enabled and events are also
enabled) when you type anything into any cell in any worksheet in that workbook.

It should exit the subroutine if your change is to multiple cells--or if your
change isn't in column E.

Hi Dave,

[quoted text clipped - 81 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

 

[Dave Peterson]

I don't have a guess at what to do with the barcoder stuff, but you can change
this portion:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub

to something like:
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
if .row < 2 then exit sub 'added this line

or you could just type in the range you want:
If Intersect(.Cells, Sh.Range("e2:e65536")) Is Nothing Then Exit Sub

(adjust that 65536 to 1meg if you're using xl2007)

Hi Dave,

Thanks for this. Typo has been fixed. I just have another question. The first
cell i.e "E1" has a title in it. So the next cell the event should affect is
"E2". How will I adjust the code to reflect this.

What happens is that a bar code reader reads the info but I need a date/time
to show when the info was read. When it is next used, the date and time of
the previous cell should not change.

Any help gratefully accepted.

Mary.

First, you'll have to fix a typo:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
End If

Becomes this:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then
Exit Sub
End If

or this:
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub

But not both!

=====
And it should run automatically (if macros are enabled and events are also
enabled) when you type anything into any cell in any worksheet in that workbook.

It should exit the subroutine if your change is to multiple cells--or if your
change isn't in column E.

Hi Dave,

[quoted text clipped - 81 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1

 

[sike11 via OfficeKB.com]

Hi Dave,

Thank you very much!! I have managed to get this working and it is great!! I
can't tell you how much I appreciate your help.

Regards,

Mary.

I don't have a guess at what to do with the barcoder stuff, but you can change
this portion:

If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub

to something like:
If Intersect(.Cells, Sh.Range("e:e")) Is Nothing Then Exit Sub
if .row < 2 then exit sub 'added this line

or you could just type in the range you want:
If Intersect(.Cells, Sh.Range("e2:e65536")) Is Nothing Then Exit Sub

(adjust that 65536 to 1meg if you're using xl2007)

Hi Dave,

[quoted text clipped - 42 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1