Populate username in next column after save

[owings.jordan]

Hi, I'm new to VBA and trying to design a macro that populates a username in a neighboring column after a save. For example, a user populates a value in cell A1. When the worksheet is saved, the user's name appears in cell B1.

Here's what I tried...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("A1:A50").Value <> "" Then
Sheets("Sheet1").Range("B1:B50").Value = Environ("username")
End If
End Sub

Thanks for your help.

[MarvinP]

Hi owings and welcome to the forum,

Did your code work? What did you expect to happen that didn't?

I'm not very good at English, but I didn't find a question mark in your post. Does that mean you don't have a question?

It is always easier for us to understand your questions if you attach a sample file with the question.
Click on "Go Advanced" below the message area and then on the Paper Clip Icon to attach a file.

[JOHN H. DAVIS]

Please use code tags when posting. Try:

Please Login or Register  to view this content.

[MarvinP]

or

Please Login or Register  to view this content.

[owings.jordan]

Thank you John & Marvin.

John, I used your code and it works like a charm... with one caveat (because i did not clearly explain the intended functionality in my previous post)...

the code overwrites everything in the column... this is a shared workbook... so for example, if a user modifies cell A1 and saves, then another user modifies cell A2 and saves, both B1 and B2 will reflect the 2nd user... how do i get the workbook to keep the first user's name in B1?

[MarvinP]

Try change this line:

If Cells(RowCtr, "A") <> "" Then Cells(RowCtr, "B") = Environ("username")

to

If Cells(RowCtr, "A") <> "" AND Cells(RowCtr, "B") = "" Then Cells(RowCtr, "B") = Environ("username")

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[owings.jordan]

Success! Thank you both for sharing your knowledge and helping me with this question!