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.

How about a worksheet_BeforeSave event?

Columns("M:M").Copy
Columns("M:M").PasteSpecial Paste:=xlPasteValues

would work

"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.

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.

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.

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

Your original post showed a lower case v, not an upper case V.

The macro I posted will look for a lower case v entered in cells K2 thru
K100. Adjust the macro if this is not the case.
--
Gary''s Student - gsnu200793


"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

On 24 Jun, 16:01, Gary''s Student
wrote:
Your original post showed a lower case v, not an upper case V.

The macro I posted will look for a lower case v entered in cells K2 thru
K100. *Adjust the macro if this is not the case.
--
Gary''s Student - gsnu200793



"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- Hide quoted text -

- Show quoted text -

Neither work, i only put an uppercase v in the most recent post to
make it stand out.
What did you mean about disabling events etc?

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

On 24 Jun, 16:14, Sam Wilson
wrote:
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- Hide quoted text -

- Show quoted text -

That doesnt work, seriously. Is there a way of you making a blank
spreadsheet and emailing it to me incase im being a total tool?
Im going to View Code though, so its not on a module. Other macros
work like that so i dont know whats going on.

The only thing i think is that it might not be drawing the username or
something??

I know what youre saying should work, ive seen things like it in
enough places, but its just not. Im lost.

On 24 Jun, 16:14, Sam Wilson
wrote:
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- Hide quoted text -

- Show quoted text -

Got it, im a total muppet.
I was putting it on the worksheet View Code, not the actual sheet.
Thanks guys.

Is there anyway i can get it throughout the whole book though, for any
sheet??

On 24 Jun, 16:26, NPell wrote:
On 24 Jun, 16:14, Sam Wilson
wrote:





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- Hide quoted text -

- Show quoted text -

Got it, im a total muppet.
I was putting it on the worksheet View Code, not the actual sheet.
Thanks guys.

Is there anyway i can get it throughout the whole book though, for any
sheet??- Hide quoted text -

- Show quoted text -

Ok, again im sorry for all the reposts.
As quick as it started working, it stopped again after i closed the
file and re-did it.

Im going nuts with this, i swear.

On 24 Jun, 16:33, NPell wrote:
On 24 Jun, 16:26, NPell wrote:





On 24 Jun, 16:14, Sam Wilson
wrote:

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- Hide quoted text -

- Show quoted text -

Got it, im a total muppet.
I was putting it on the worksheet View Code, not the actual sheet.
Thanks guys.

Is there anyway i can get it throughout the whole book though, for any
sheet??- Hide quoted text -

- Show quoted text -

Ok, again im sorry for all the reposts.
As quick as it started working, it stopped again after i closed the
file and re-did it.

Im going nuts with this, i swear.- Hide quoted text -

- Show quoted text -

I got it working again, but i got a debug error after accidentally
pasting something. Which is something i got before.
How can i undo the error to get it running again??

OK, but I'll need your e-mail address


"NPell" wrote:

On 24 Jun, 16:14, Sam Wilson
wrote:
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- Hide quoted text -

- Show quoted text -

That doesnt work, seriously. Is there a way of you making a blank
spreadsheet and emailing it to me incase im being a total tool?
Im going to View Code though, so its not on a module. Other macros
work like that so i dont know whats going on.

The only thing i think is that it might not be drawing the username or
something??

I know what youre saying should work, ive seen things like it in
enough places, but its just not. Im lost.

After the line application.enableevents=false add another line:

"On Error GoTo ErrorCatcher"

Before the line application.enableevents=true add a line:

"ErrorCatcher:"

"NPell" wrote:

On 24 Jun, 16:33, NPell wrote:
On 24 Jun, 16:26, NPell wrote:





On 24 Jun, 16:14, Sam Wilson
wrote:

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- Hide quoted text -

- Show quoted text -

Got it, im a total muppet.
I was putting it on the worksheet View Code, not the actual sheet.
Thanks guys.

Is there anyway i can get it throughout the whole book though, for any
sheet??- Hide quoted text -

- Show quoted text -

Ok, again im sorry for all the reposts.
As quick as it started working, it stopped again after i closed the
file and re-did it.

Im going nuts with this, i swear.- Hide quoted text -

- Show quoted text -

I got it working again, but i got a debug error after accidentally
pasting something. Which is something i got before.
How can i undo the error to get it running again??

To have the event run from any worksheet...............

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
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

Right-click on the Excel Icon left of "File" on the worksheet menubar and "View
Code"

Copy/paste the above into that module.

Delete any existing code from worksheets.


Gord Dibben MS Excel MVP

On Tue, 24 Jun 2008 08:26:53 -0700 (PDT), NPell wrote:

Is there anyway i can get it throughout the whole book though, for any
sheet??

On 24 Jun, 17:48, Gord Dibben <gorddibbATshawDOTca wrote:
To have the event run from any worksheet...............

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
* *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

Right-click on the Excel Icon left of "File" on the worksheet menubar and "View
Code"

Copy/paste the above into that module.

Delete any existing code from worksheets.

Gord Dibben *MS Excel MVP



On Tue, 24 Jun 2008 08:26:53 -0700 (PDT), NPell wrote:
Is there anyway i can get it throughout the whole book though, for any
sheet??- Hide quoted text -

- Show quoted text -

This is all brilliant, thankyou everyone.
Its all working nicely.
Just a quick question though, if i select a few cells and put a V in
and press Ctrl+Enter to fill them. It comes up with an error. Can I
not fill in more than one cell at a time?