First Name only

[blee4372]

I have a macro that get me Date / time & the username's full name in this format "Doe, John". What I want is to just get the first name "John". Here is the macro I am using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
With ws.Range("G1")
.Value = Now
.NumberFormat = "ddmmmyy hh:mm"
End With
Set WSHnet = CreateObject("WScript.Network")
UserName = WSHnet.UserName
UserDomain = WSHnet.UserDomain
Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
UserFullName = objUser.FullName
ws.Range("H1") = objUser.FullName

End Sub

 

[Nine Zero]

try this maybe

Dim firstName as String
Dim space As Integer
space = InStrRev(UserFullName, " ")
firstName= Right(UserFullName, Len(UserFullName) - space)

 

[Joe4]

This should return what you want:

FirstName= Trim(Mid(UserFullName, InStrRev(UserFullName, ",") + 1))

 

[steve the fish]

That would depend if all users that could use the file had the same format of name ie Last, First. But something like this does your format:

ws.Range("H1") = Trim(Split(UserFullName, ",")(1))

 

[blee4372]

That would depend if all users that could use the file had the same format of name ie Last, First. But something like this does your format:

ws.Range("H1") = Trim(Split(UserFullName, ",")(1))

Thanks Steve the fish, this was what I was look for
Thank everyone for their reply

 

[Nine Zero]

That would depend if all users that could use the file had the same format of name ie Last, First. But something like this does your format:

ws.Range("H1") = Trim(Split(UserFullName, ",")(1))

@steve the fish
WHOA!!!
Please tell me that the (1) in your code returns the second index of the split...... cuz if thats true then that is freaking amazing!!!! i never knew that!

 

[Joe4]

Please tell me that the (1) in your code returns the second index of the split...... cuz if thats true then that is freaking amazing!!!! i never knew that!

Yeah, I forgot about the SPLIT function. It definitely is the most efficient way of doing it.
And yes, 1 returns the second field in the split, as the index is zero-based (so 0 would return the first field).

 

[Nine Zero]

Yeah, I forgot about the SPLIT function. It definitely is the most efficient way of doing it.
And yes, 1 returns the second field in the split, as the index is zero-based (so 0 would return the first field).

Mind....Blown

 

[blee4372]

@steve the fish
WHOA!!!
Please tell me that the (1) in your code returns the second index of the split...... cuz if thats true then that is freaking amazing!!!! i never knew that!

It returned the first name only.