Static Data Updating
 

I found the problem. With the code I will post below, the static date and
time works as iit shoud, however, whenever I use the "data filter auto
filter" from the menu, all cells update to current date and time. Is there
anything I can add to the code that will prevent the date and time from
updating when I use the "datafilterauto filter" feature I am not sure if
it happens when I select the feature that adds the arrows to the top row, or
when I select one of the arrows to sort by.

Thanks in advance

Static Data Updating
 

You forgot to post the code. However, your comments suggested that you have
posted on this issue previously so I searched for your previous posts and I
think that the answer could be as follows which will only update if the cell
is blank.

If not the answer then post the code and will have another look.

If Target.Column = 4 Or Target.Column = 6 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1) = Now()
End If
End If

If the column is formatted as a date you could probably also use
If Target.Offset(0, 1) = 0 Then

--
Regards,

OssieMac


"MilleniumPro" wrote:

I found the problem. With the code I will post below, the static date and
time works as iit shoud, however, whenever I use the "data filter auto
filter" from the menu, all cells update to current date and time. Is there
anything I can add to the code that will prevent the date and time from
updating when I use the "datafilterauto filter" feature I am not sure if
it happens when I select the feature that adds the arrows to the top row, or
when I select one of the arrows to sort by.

Thanks in advance

Static Data Updating
 

Hello Ossie Mac,

I Tried the code that you posted and it changed away from what I wanted.
This is the code that I posted in a different related post that works the
way I want with the exception of the updating of the date and time that is
taking place when I use the datafilterautofilter featu

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Target is an arguments which refers to the range being changed
If Target.Column = 1 Or Target.Column = 2 Then
'this check is to prevent the procedure from repeatedly calling itself
If Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)


End If
If Target.Column = 4 Or Target.Column = 6 Then
Target.Offset(0, 1) = Now()
End If



End If
End Sub


The code you provided did not allow for a timestamp to be entered in column
E when a value was manually entered into column D. It only placed a time
stamp in Column G when a value was entered in column F.

Lets look at this like a time sheet. Where when someone comes to work, I
want to enter a value in column D to let me know they arrived. When I enter
that value, I want the time stamp to apper and remain static in Column E.
Later in the day, I want to enter a value in Column F that says they are
leaving. When I enter that value, I want the timestamp to be entered in
Column G.

I use the dataFilterauto filter feature because In the first column of the
sheet, I have only 2 values, either I for in and O for out. as people come
in and out I rechoose the datafilterauto filter feature to show me only
people that have a value of I in the first colum. It is when I choose the
feature and sort that causes all timestamps to update to the current time,
which t otally defeats the purpose of what I am trying to do.

If you could, please modify the entire code I am pasting as I have been
picking peices here and there that got me to where I am so far with no idea
how to edit or write code from scratch. Thanks for your help.



The only fix I need for this is to allow for the cells not to update
"OssieMac" wrote in message
...
You forgot to post the code. However, your comments suggested that you
have
posted on this issue previously so I searched for your previous posts and
I
think that the answer could be as follows which will only update if the
cell
is blank.

If not the answer then post the code and will have another look.

If Target.Column = 4 Or Target.Column = 6 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1) = Now()
End If
End If

If the column is formatted as a date you could probably also use
If Target.Offset(0, 1) = 0 Then

--
Regards,

OssieMac


"MilleniumPro" wrote:

I found the problem. With the code I will post below, the static date and
time works as iit shoud, however, whenever I use the "data filter auto
filter" from the menu, all cells update to current date and time. Is
there
anything I can add to the code that will prevent the date and time from
updating when I use the "datafilterauto filter" feature I am not sure
if
it happens when I select the feature that adds the arrows to the top row,
or
when I select one of the arrows to sort by.

Thanks in advance

Static Data Updating
 

You have me confused now. I previously assumed that the AutoFilter was firing
the Worksheet_Change event. However, I can't get the Worksheet_Change event
to fire with AutoFilter.

I have tried all of the following in both xl2002 and xl2007.
Turn AutoFilter on and off.
Set Filters after turning on AutoFilter.
Inserted Subtotal functions so their values change when filters are set.

It almost appears that you have got the formula =NOW() in the cells instead
a fixed value being inserted by the code. If this is the case then it will
update every time the worksheet calculates.

As a test insert the following line of code as the first line after the Sub
name to establish if the event is being called and if so what cell is causing
it to be called.

MsgBox Target.Address


In the code you posted, the following lines will never be processed.

If Target.Column = 4 Or Target.Column = 6 Then
Target.Offset(0, 1) = Now()
End If
It is inside the loop that tests for column 1 or 2 and therefore if it is
column 4 or 6 then it will be bypassed by the previous test.

You need an Else in the code like below. (Also disable events and re Enable
at end to prevent recursive calls to the event.)

Private Sub Worksheet_Change(ByVal Target As Range)
'Target is an arguments which refers to the range being changed

Application.EnableEvents = False

If Target.Column = 1 Or Target.Column = 2 Then
'this check is to prevent the procedure from repeatedly calling itself
If Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)
End If

Else

If Target.Column = 4 Or Target.Column = 6 Then
Target.Offset(0, 1) = Now()
End If

End If

Application.EnableEvents = True

End Sub

If the above code fails before reaching the enableEvents = True the events
remain turned off. Insert the following code anywhere in the VBA editor (Just
under the above code is OK) and run it from the VBA editor to turn the events
back on.

Sub Re_EnableEvents()
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"MilleniumPro" wrote:

Hello Ossie Mac,

I Tried the code that you posted and it changed away from what I wanted.
This is the code that I posted in a different related post that works the
way I want with the exception of the updating of the date and time that is
taking place when I use the datafilterautofilter featu

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Target is an arguments which refers to the range being changed
If Target.Column = 1 Or Target.Column = 2 Then
'this check is to prevent the procedure from repeatedly calling itself
If Not (Target.Text = UCase(Target.Text)) Then
Target = UCase(Target.Text)


End If
If Target.Column = 4 Or Target.Column = 6 Then
Target.Offset(0, 1) = Now()
End If



End If
End Sub


The code you provided did not allow for a timestamp to be entered in column
E when a value was manually entered into column D. It only placed a time
stamp in Column G when a value was entered in column F.

Lets look at this like a time sheet. Where when someone comes to work, I
want to enter a value in column D to let me know they arrived. When I enter
that value, I want the time stamp to apper and remain static in Column E.
Later in the day, I want to enter a value in Column F that says they are
leaving. When I enter that value, I want the timestamp to be entered in
Column G.

I use the dataFilterauto filter feature because In the first column of the
sheet, I have only 2 values, either I for in and O for out. as people come
in and out I rechoose the datafilterauto filter feature to show me only
people that have a value of I in the first colum. It is when I choose the
feature and sort that causes all timestamps to update to the current time,
which t otally defeats the purpose of what I am trying to do.

If you could, please modify the entire code I am pasting as I have been
picking peices here and there that got me to where I am so far with no idea
how to edit or write code from scratch. Thanks for your help.



The only fix I need for this is to allow for the cells not to update
"OssieMac" wrote in message
...
You forgot to post the code. However, your comments suggested that you
have
posted on this issue previously so I searched for your previous posts and
I
think that the answer could be as follows which will only update if the
cell
is blank.

If not the answer then post the code and will have another look.

If Target.Column = 4 Or Target.Column = 6 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1) = Now()
End If
End If

If the column is formatted as a date you could probably also use
If Target.Offset(0, 1) = 0 Then

--
Regards,

OssieMac


"MilleniumPro" wrote:

I found the problem. With the code I will post below, the static date and
time works as iit shoud, however, whenever I use the "data filter auto
filter" from the menu, all cells update to current date and time. Is
there
anything I can add to the code that will prevent the date and time from
updating when I use the "datafilterauto filter" feature I am not sure
if
it happens when I select the feature that adds the arrows to the top row,
or
when I select one of the arrows to sort by.

Thanks in advance

Static Data Updating
 

Where I said "It is inside the loop that tests for column 1 or 2"
should read "It is inside the If/Then/End If test for column 1 or 2"
it is not a loop.
--
Regards,

OssieMac