Allowing only one phone number input format.

[FrancisM]

ok. here is the adjusted code. It is still not formatting. There is one thing else, I did. I made a second module. Was that the correct thing to do?

Code: Select all

ivate Sub Worksheet_Change(ByVal Target As Range)
' the cell being monitored
    If Target.Address <> "$H$21" Or Target.Value = "" Then Exit Sub
' unprotect sheet
    ActiveSheet.Unprotect
' prevent this procedure from calling itself
    Application.EnableEvents = False
' change what's in the cell
    Target.Value = onlyNumbers(Target.Value)
    Target.NumberFormat = "000-00-0000"
' re-enable events
    Application.EnableEvents = True
' re-protect sheet
    ActiveSheet.Protect
End Sub


Private Function onlyNumbers(myVal As Variant) As Variant
    Dim i   As Integer
    For i = 1 To Len(myVal)
        If IsNumeric(Mid(myVal, i, 1)) Then onlyNumbers = onlyNumbers & Mid(myVal, i, 1)
    Next i
End Function

Is there something, I am missing, or doing wrong?

[NoSparks]

There can only be one Worksheet_Change procedure.
All cells to be dealt with must be handled in the same procedure.
Just like the code I posted last post.

[FrancisM]

I made the correction to the line that read 000-000-0000. The line now reads 000-00-0000. I did it as a second module, should I have done that? there still has not been any change. I have added a copy of the most recent application.

[NoSparks]

I did it as a second module, should I have done that?

No.
Not with the first one.
Not with the second one either.
Sheet events only run from sheet module of the sheet they pertain to.

Repair what you've got like this:
Right click on the Form sheet tab
click view code
delete everything from this sheets module except Option Explicit.

Double click Module2
copy everything from Module2
go back to the Form sheets module
paste everything from Module2 into the Form sheet module.

Right click on Module1
select remove module1
click no when asked if export before removal.

Right click on Module2
select remove module2
click no when asked if export before removal.

[FrancisM]

NoSparks. Thank you for both making the correction & explaining what I did wrong. It works perfectly.