Extract first and last name from email & Runtime error 13 type mismatch

[Pookiemeister]

I might be overthinking this but I have 3 textboxes on a userform. So the way we setup our email address is
firstname.lastname@company.com
textbox1 = first name
textbox2 = last name
textbox3 = email address

I'm trying to reverse our company's email address format. So if the user enters the email address, the code will extract the first and last name and put them in their appropriate textboxes. I was able to fill the first name textbox but not the last name textbox.

Private Sub TextBox1_Change()
    If (Me.TextBox1.Text) = "" Then Me.TextBox3 = Me.TextBox1.Text & "."
End Sub

Private Sub TextBox2_Change()
    If (Me.TextBox2.Text) = "" Then Me.TextBox3 = Me.TextBox1.Text & "." & Me.TextBox2.Text & "@sanofi.com"
End Sub

Private Sub TextBox3_Change()
    Dim str As String
   
    str = Me.TextBox3.Text
    If InStr(Me.TextBox3.Text, ".") > 0 Then Me.TextBox1.Text = Left(Me.TextBox3.Text, InStr(Me.TextBox3.Text, ".") - 1)
    If InStr(str, "@") > 0 Then Me.TextBox2.Text = Right(str, InStr(str, "@"))
   
End Sub
Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Me.TextBox1.Enabled = False
    Me.TextBox2.Enabled = False
    Me.TextBox3.Enabled = True

End Sub
Private Sub UserForm_Initialize()
    Me.TextBox1.Enabled = True
    Me.TextBox2.Enabled = True
    Me.TextBox3.Enabled = False
End Sub

Also, when I hover over this part of the code I find where the error 13 type mismatch occurs.

Me.TextBox2.Text = Right(str, InStr(str, "@")) - 1  [B]'InStr(str, "@")=13[/B]

Why does it occur?
How can I fix it so it does what I want it to do.

Thank you.

 

[Micron]

The 'why' boils down to trying to put one data type into a place that won't accept it. That destination can be a function, variable or anything else.
Note - you can't format code posted in code tags, so best to just add a comment somewhere, like the end of the line.
Try this trouble shooting technique:
Debug.Print Right(str, InStr(str, "@")) - 1
to ensure it is what you think it is. Step through and see what the output is when the line is executed.

Note: what format is used in cases like Michael.St.John? It looks like your code would not be adversely affected by that but might be worth considering. That and hyphenated names.

EDIT - after a closer look I don't see this line in your code
Me.TextBox2.Text = Right(str, InStr(str, "@")) - 1

 

[Alex Blakenburg]

Also the code doesn't deal with all scenarios.
eg instr using "." if missing between the first and last name will find the "." in ".com"
What to do if the first "." is missing ? Do you assume its the first name or the last name ?
(the below assumes its the first name)
What to do if the @ symbol is missing ? - the below assumes that the @company.com part is missing

How about using split ?

Private Sub TextBox3_Change()
    Dim str As String
    Dim strSplit As Variant
 
    str = Me.TextBox3.Text
    strSplit = Split(str, "@")          ' Split string into before and after @
    strSplit = Split(strSplit(0), ".")  ' Split before part into before and after "."
    
    If UBound(strSplit) > 0 Then
        ' if the "." exists then presumably there is a first and last name
        Me.TextBox1.Text = strSplit(0)
        Me.TextBox2.Text = strSplit(1)
    Else
        ' if the "." does not exists assume its the first name
        Me.TextBox1.Text = strSplit(0)
        Me.TextBox2.Text = ""
    End If

End Sub

In your code:
• you have this " str = Me.TextBox3.Text"
if you then consistently use str it will make the code easier to read and it will be easier to put in test values to test.
At the moment your first If statement refers to Me.TextBox3.Text twice while the If statement after it uses str
• To get Last Name you need between the first "." and the "@" which is not covered by Right(str, InStr(str, "@"))