What this Private Sub does?

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellContents As String
    Dim valLength As Integer
    cellContents = Trim(Str(Val(Target.Value)))
    valLength = Len(cellContents)
    If valLength <> 3 Then
        MsgBox ("Please enter a 3 digit area code.")
        Cells(9, "C").Select
    Else
        Cells(9, "C").Value = cellContents
        Cells(9, "D").Select
    End If
End Sub

---

It's an event handler. When the worksheet changes, it runs. It checks a cell and makes sure that the contents has a length of three. No more, no less. Thou shalt not count one, nor shall thou count two unless proceeding directly to three. Five is right out!

-tg

bravo! bravo! lol i love monty python

I would also suggest adding the .EnableEvents Line

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellContents As String
    Dim valLength As Integer
    cellContents = Trim(Str(Val(Target.Value)))
    valLength = Len(cellContents)
   
    Application.EnableEvents = False
   
    If valLength <> 3 Then
        MsgBox ("Please enter a 3 digit area code.")
        Cells(9, "C").Select
    Else
        Cells(9, "C").Value = cellContents
        Cells(9, "D").Select
    End If
   
    Application.EnableEvents = True
End Sub

---

Sid