Update username

[Alejandro]

Hello there,

I have a function in a module that retrieves the system's username. Hereit is:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function


Now, this is a copy&paste from another example, I won't pretend that I
understand how this function works, but it does work. When I created this
workbook I added in cells several spreadsheets the formula =fosusername() and
indeed those cells showed my username. The problem I'm having is that now
when someone else opens the file it still shows my username.

I know that adding code for every single cell in every single spreadsheet
where I need the username will work (Range("XX")=fosusername()) but...is
there a way to "update" the value of this function, for example, when the
workbook is opened, so that I will not have to add code in all spreadsheets?

Thanks!

A.

 

[Barb Reinhardt]

Have you tried

fOSUserName = Environ("USERNAME")

HTH,
Barb Reinhardt

 

[Alejandro]

You mean creating a new fOSUserName function using that code line instead of
my function? Whe would I use it?

I commented out my original function and created a new one in the same
module, but that didn't update the values of the cells in the spreadsheets.

 

[eliano]

You mean creating a new fOSUserName function using that code line insteadof
my function? Whe would I use it?

I commented out my original function and created a new one in the same
module, but that didn't update the values of the cells in the spreadsheets.








- Mostra testo citato -

Barb indication is correct and this work for me:

Function fOSUserName() As String
fOSUserName = Environ("USERNAME")
End Function

Eventually close the file and open that again.
Saluti
Eliano

 

[p45cal]

Function fOSUserName()
fOSUserName = Environ("UserName")
End Function

in any standard code module.

Use on a worksheet thus:
=fOSUserName(

 

[Alejandro]

Thank you!

However, your answer confirms what i stated initially (that in every
spreadsheet I'd have to enter the code line: Range("XX").value=fOSUserName()
) which is what I was trying to avoid since I have so many spreadsheets using
this function. From what you're saying it seems that there is no way to call
that function and update the value of fOSUserName when the workbook opens,
for example, instead of calling the function in every single worksheet.

 

[p45cal]

Won't


Private Sub Workbook_Open()
Calculate
End Sub

in the ThisWorkbook code module do it?

I can't easily test where I am at the moment

 

[eliano]

Thank you!

However, your answer confirms what i stated initially (that in every
spreadsheet I'd have to enter the code line: Range("XX").value=fOSUserName()
) which is what I was trying to avoid since I have so many spreadsheets using
this function. From what you're saying it seems that there is no way to call
that function and update the value of fOSUserName when the workbook opens,
for example, instead of calling the function in every single worksheet.







- Mostra testo citato -

Probably I have not understood; however try this macro in the module:
ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = Environ("USERNAME")
End Sub

Regards
Eliano

 

[eliano]

Thank you!

However, your answer confirms what i stated initially (that in every
spreadsheet I'd have to enter the code line: Range("XX").value=fOSUserName()
) which is what I was trying to avoid since I have so many spreadsheets using
this function. From what you're saying it seems that there is no way to call
that function and update the value of fOSUserName when the workbook opens,
for example, instead of calling the function in every single worksheet.

:


Probably I have not understood; however try this macro in the module:
ThisWorkbook

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = Environ("USERNAME")
End Sub

Regards
Eliano- Nascondi testo citato

- Mostra testo citato -

As names involved in Windows and Office could be two, try also:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = Application.UserName
End Sub

Saluti
Eliano