Appear username when open worksheet

[noelmus]

I have a worksheet named "MINE" and need that when I open it my username appears in cell A1. I need that my username will be full name, example Tony Smith.

Thanks in advance

 

[Mentor82]

I have a worksheet named "MINE" and need that when I open it my username appears in cell A1. I need that my username will be full name, example Tony Smith.

Thanks in advance

Hi,
I assume that you want to have appeared excel application user name. For this use the following:

Sub AppearUsrName()
Dim ws as worksheet

Set ws = worksheets("MINE")
If ActiveSheet.Name=ws.Name then
 ws.range("A1")=Application.UserName
End if

Set ws = nothing
End sub

 

[noelmus]

Hi
I tried the code in worksheet and even in workbook and nothing appeared when I opened the worksheet.
Thanks

 

[noelmus]

Sorry
It worked but you have to run it manually. I need it automatically and where I will put the code, in workbook or worksheet?
Thanks.

 

[Mentor82]

So sorry. You have to go to the workbook module and type in below code. It's needed to use workbook_sheetactivate event if the macro is to run when you change the sheet to MINE.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws as worksheet

Set ws = worksheets("MINE")
If ActiveSheet.Name=ws.Name then
 ws.range("A1")=Application.UserName
End if

Set ws = nothing

End Sub

 

[Mentor82]

If the previous code won't work try this. This one should-the previous one should work too. I'm writing the code for you on my mobile so I'm not able to debug the code I'm writing.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws as worksheet

Set ws = worksheets("MINE")
If Sh.Name=ws.Name then
 ws.range("A1")=Application.UserName
End if

Set ws = nothing

End Sub

 

[noelmus]

Thanks a lot Mentor82

 

[Mentor82]

Happy to help
Thanks for your feedback