Hi,

You right-click on the sheet VBA Project explore, view code and add ths:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Columns("K:K"), Target) Is Nothing Then
If UCase(Target.Value) = "V" Then
Target.Offset(0, 2).Value = Environ("UserName")
else
Target.Offset(0, 2).Value = ""
end if
End If

Application.EnableEvents = True

End Sub

What Gary is saying is that if the "V" is not entered by a user, but changes
as the result of a formula, the formula won't run. As long as someone
manually enters the "v" it'll be fine.


"NPell" wrote:

On 24 Jun, 15:30, NPell wrote:
On 24 Jun, 15:17, Gary''s Student





wrote:
First to your last point about the Change event not working. There are four
things you must take care of:

1. The event macro must go in the worksheet code are, not a standard module

2. The change event will not trigger on a cell that changes due to a formula
in that cell changing value. You need to use a Calculate macro for that.

3. If you are changing cells within the macro, be sure to disable events
before doing so and re-enable events after doing so.

Let's assume that K2 changes value by entry, not formula. Try the following
worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set k = Range("K2:K100")
Set t = Target
If Intersect(t, k) Is Nothing Then Exit Sub
If t.Value < "v" Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 2).Value = Environ("username")
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200793

"NPell" wrote:
Hello,
This is an ongoing issue for me. Im making small steps though.

I currently have this in cell M2 and all the way down;
=IF((K2="v"),(user()),"")

With the module;
Public Function user() As String
user = Environ("username")
End Function

This works fine, however, when someone else uses the spreadsheet it
replaces my username with theres wherever there is a V.
Can i make this a constant value when entered, like a Paste Value. Not
something that re-freshes.

Maybe a Worksheet Change that works, because others in the past havent
triggered.

Thanks if you can help.- Hide quoted text -

- Show quoted text -

Gary's Student.
I have tried your formula, but it didnt work.
I dont understand what you mean about it not triggering on the cell
that changes?
Is it maybe not working because im on a network? or is that
irrelevant. Its Excel 2003, btw.

Sam Wilson.
That might be a good alternative. Do you have a macro for this?

Thanks guys.- Hide quoted text -

- Show quoted text -

Sorry for the re-psot, i just wanted to be clear.

Basically its;
When a V is entered in K:K .. populate the Username in M:M on the same
row.
I work up to 2500 rows for other parts, so if it needs to be limited
it can to that.

Thanks