String Functions: Deleting Last Name and Trailing Text After a Space

[mdever74]

Hello:This is my first post on this site. So forgive me if this has already been explained and I would ask for help to locate that post.

Basically (and to try and be less verbose as I usually am), I am working on a project for my employer where I am designing an "interview" process that will populate fields on various worksheets in an Excel workbook. I don't want the end user to see the workbook at all to avoid risking the integrity of the formulas within the worksheets.

To start off the interview process (where users will be guided through a series of user forms where they will enter date that populates respective cells in the workbook's worksheets), I have a splash page welcoming the user. I

am trying to make it as inviting as I can, so I began the code with the following:

Private Sub userform_activate()

Application.OnTime Now + TimeValue("00.00.04"), "CloseForm"

lblWelcome.Caption = "Welcome"

lblStarted.Caption = "Let's Get Started"

End Sub

I want to add another label that will take the name of the individual user who is going through the interview process (Application.Username), but trim the string to only show the first name. The problem is, in my organization, a person's name (in any MS Office application) is structured as follows:last name, first name (space) location

How would I dissect the username to just show the first name?

I created this function to clear the last name but am at a loss for how to clear the trailing text after the first name.

Function clearLast(fullname As String) As String

Dim spacePos As IntegerspacePos = InStr(fullname, ", ")
clearLast = Mid$(fullname, spacePos + 1, Len(clearLast) - spacePos)

End Function

Any help would be greatly appreciated.

- Matt

 

[Fishboy]

Hi mdever74, welcome to the boards.

I am not sure how to incorporate this into your existing setup, but to answer your question on how to extract the correct information from the "Last Name, First Name Location" string see below.

The formulaic way to do this (assuming for example's sake that the string is in cell A1) is to use this formula:

=MID(A1, FIND(" ", A1) + 1, FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1))

To convert this into usable VBA you can amend it to this (note that all quotation marks except the outermost 2 have all been doubled up, as in " becomes "" and "" becomes """" and so on):

Application.Evaluate("=Mid(A1, Find("" "", A1) + 1, Find("" "", A1, Find("" "", A1) + 1) - Find("" "", A1))")

To test this out I just stuck in a message box:

Sub TEST()
MsgBox Application.Evaluate("=Mid(A1, Find("" "", A1) + 1, Find("" "", A1, Find("" "", A1) + 1) - Find("" "", A1))")
End Sub

If that works for you to find the correct part of the text you are looking for, I suspect you can user the Application.Evaluate method above to get the right information into your userform.

 

[mdever74]

Good day, Fishboy:

After playing around with your code and referencing my "Abo****e Beginner's Guide to VBA", I discovered the solution. Here is the code that solved the problem:

Private Sub UserForm_activate()

Dim firstSpace As Integer
Dim secondSpace As Integer
Dim currUser As String
firstSpace = InStr(Application.userName, " ")
secondSpace = InStr(firstSpace + 1, Application.userName, " ")
currUser = Mid(Application.userName, firstSpace + 1, secondSpace - firstSpace - 1)



Application.OnTime Now + TimeValue("00.00.04"), "CloseForm"
lblWelcome.Caption = "Welcome"
lblActiveUser.Caption = currUser
lblStarted.Caption = "Let's Get Started"


End Sub


Thank you for your input, though. Between your direction and my book, I was able to resolve the problem.

 

[Fishboy]

Good day, Fishboy:

After playing around with your code and referencing my "Abo****e Beginner's Guide to VBA", I discovered the solution. Here is the code that solved the problem:

Private Sub UserForm_activate()

Dim firstSpace As Integer
Dim secondSpace As Integer
Dim currUser As String
firstSpace = InStr(Application.userName, " ")
secondSpace = InStr(firstSpace + 1, Application.userName, " ")
currUser = Mid(Application.userName, firstSpace + 1, secondSpace - firstSpace - 1)



Application.OnTime Now + TimeValue("00.00.04"), "CloseForm"
lblWelcome.Caption = "Welcome"
lblActiveUser.Caption = currUser
lblStarted.Caption = "Let's Get Started"


End Sub


Thank you for your input, though. Between your direction and my book, I was able to resolve the problem.

Happy I could at least point you in the right direction. Glad you got it working mate.

 

[Peter_SSs]

Welcome to the MrExcel board!

Seems like you are operational, but this might be a more direct way to the first name without the need for firstSpace, secondSpace etc
Try

currUser = Split(Application.UserName)(1)