Username in Excel - Paste Value?

[NPell]

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.

 

[Sam Wilson]

How about a worksheet_BeforeSave event?

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

would work

 

[Gary''s Student]

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

 

[NPell]

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










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

 

[NPell]

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

 

[Gary''s Student]

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.

 

[NPell]

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








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

 

[Sam Wilson]

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]

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.








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

 

[NPell]

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.








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

 

[NPell]

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.

 

[NPell]

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

 

[Sam Wilson]

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

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.

 

[Sam Wilson]

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

"On Error GoTo ErrorCatcher"

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

"ErrorCatcher:"

 

[Gord Dibben]

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

 

[NPell]

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





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