Problem with event handler macro.

[brans1982]

Hi,

Could someone take a look at this.

I created this Event Handler to add "-" in the middle of a 6 digit string...

e.g aaabbb will format as AAA-BBB.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
Case 9
Routing = Target.Value
Select Case Len(Routing)
Case 6
NewRouting = UCase(Left(Routing, 3) & "-" & Right(Routing, 3))
End Select

Target.Value = NewRouting

End Select

End Sub


Problem is, after typing data into the cell and hitting enter, I then have reselect the cell fot the macro to run.

Any ideas?

Thanks!

 

[VoG]

You are using the wrong event. Try

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
    Case 9
        Application.EnableEvents = False
        Routing = Target.Value
            Select Case Len(Routing)
                Case 6
                    NewRouting = UCase(Left(Routing, 3) & "-" & Right(Routing, 3))
            End Select

        Target.Value = NewRouting
        Application.EnableEvents = True
    End Select

End Sub

 

[brans1982]

Thanks Peter,

That worked perfect.

Is it possible to have two event handlers on one worksheet?

If so, how would I put this in...

Select Case Target.Column
Case 4
Application.EnableEvents = False
Order = Target.Value
Select Case Len(Order)
Case 11
NewOrder = Left(Order, 3) & "-" & Right(Order, 8))
End Select

Target.Value = NewOrder
Application.EnableEvents = True
End Select

Thanks!