How to update a cell with a value before User closes or saves the workbook?

[inddon]

Hello There,

I would like to update a cell in a specific worksheet with the userid value. This should occur when the user saves or closes and saves the workbook

Below code I have written, and the save popup keeps coming up.

Could you please advice how my requirement could be achieved?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Set ws = Worksheets("Main Menu")
   'UserId is a global variable  
   ws.Range("R3").Value = UserId
  Cancel = True
End Sub

Thanks & regards,
Don

 

[AlanSidman]

I would change this line of code

ws.Range("R3").Value = UserId

to

ws.Range("R3").Value = Environ("UserName")

 

[inddon]

I would change this line of code

ws.Range("R3").Value = UserId

to

ws.Range("R3").Value = Environ("UserName")

Hi Alan,

Thank you for your response.

I am retrieving the concatenated value of the email id from the outlook, which works fine.

I am looking for the following to achieve:
1. When the User clicks on 'Save,'it should assign this value to a cell and then the workbook should get saved.

Right now, with my above code, I close the application and it asks for 'Save', Don't save and Cancel. When I click Save, this popup keeps on appearing.

Could you let me know how can I acheive the above point 1?

Thanks
Don

 

[AlanSidman]

Don
Sorry I missed that. Perhaps adding this to your code at the beginning.

Application.DisplayAlerts = False