Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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

Username in Excel - Paste Value?
 

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

Username in Excel - Paste Value?
 

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?

Username in Excel - Paste Value?
 

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

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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??

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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??

Username in Excel - Paste Value?
 

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.

Username in Excel - Paste Value?
 

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??

Username in Excel - Paste Value?
 

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??

Username in Excel - Paste Value?
 

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?