Let me preface this by saying I am completely new to the world of VBA. A coworker had started to design a "Hire enrollment form" that would guide new employees through the onboarding prcoess, and then allow us to import that data from the excel file into Access.

Well, I sort of took over because he had a lot of other things come up. He had enough of it done that with a bit of direction and Google, I could pretty much figure out what I needed to do to add on to what he started, despite never have even hearing of VBA a few weeks ago.

The project is almost done except for one really annoying thing:

I can not get the fields to format social security numbers (and phone numbers, using basically the same code) correctly!!

It's set up as a text box. The code is thus (for SSN):
Private Sub SSnum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With SSnum
        .Tag = Format(Val(Application.Substitute(.Text, "-", vbNullString)), "###-##-####")
        Cancel = Not (.Tag Like "###-##-####")
        If Cancel Then
            MsgBox "Invalid Social Security Number"
        Else
            .Text = Format(SSnum.Value, "###-##-####")
        End If
    End With
End Sub
and
Private Sub PHnum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With PHnum
        .Tag = Format(Val(Application.Substitute(.Text, "-", vbNullString)), "(###)###-####")
        Cancel = Not (.Tag Like "(###)###-####")
        If Cancel Then
            MsgBox "Invalid Phone Number"
        Else
            .Text = Format(PHnum.Value, "(###)###-####")
        End If
    End With
End Sub
for phone numbers.

Now, this is something the coworker who started it entered, not me. Everything I've read in various forums would lead me to believe it should work. And here's the REALLY odd thing: it does. Until you exit excel and reopen it. OR enter values, then go back and delete them, and re-enter them (on the userform, of course. The spreadsheet data is to be hidden when the program is running for employees to use) Then it does... nothing. BUT, if you delete the code and the textbox (and the cell it fills into on the spreadsheet), and put new ones in... it works. Once again, until you either replace the data or exit and re open excel. It's not just my computer, it's the other computers in the office. Does the same thing on 2007 (which I use) and 2010 (which a different coworker uses).

Oh, and I've tried formatting the corresponding spreadsheet cell to general, social security number, and number. The SSN will format it right in the cell... but I want it to look nice on the userform, since that's what the employees will see.

What's really odd, is I have the same code on a different userform in the program and it seems to work fine, every time. The only difference is the name of the textbox.

When you reply, please keep in mind I am so very new to this so make it easy for me to understand. I'm fairly bright in general lol, but I am probably not familiar with technical terms.

Thanks in advance for the help!