Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On change event
Hello. Please help me if you can.
I'm trying to set up an on sheet change, which I can get to fire, but not successfully. My code is supposed to reformat a range of cells from this: 123123123412345612312123 to this: 123.123.1234.123456.123.12.123 It must also ensure that the pattern is entered correctly, i.e. not text or a partial set of values, etc. Here is my code in a sheet module (you should be able to copy directly to a module): Private Sub Worksheet_Calculate() Dim rngCell As Range For Each rngCell In Range("A24:A30").Rows If Not rngCell.FormulaR1C1 = "" And Not _ rngCell.FormulaR1C1 Like "###.###.####.######.###.##.###" Then rngCell.Activate MsgBox "Invalid account distribution!", vbCritical, _ "INVALID ACCOUNT DISTRIBUTION!": Exit Sub End If Next rngCell End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngCell As Range Dim strAccount As String For Each rngCell In Range("A24:A30").Rows If rngCell.FormulaR1C1 Like "########################" Then strAccount = rngCell.FormulaR1C1 strAccount = Left(strAccount, 3) & "." & _ Mid(strAccount, 4, 3) & "." & _ Mid(strAccount, 7, 4) & "." & _ Mid(strAccount, 11, 6) & "." & _ Mid(strAccount, 17, 3) & "." & _ Mid(strAccount, 20, 2) & "." & _ Right(strAccount, 3) rngCell.FormulaR1C1 = strAccount End If Next rngCell End Sub Your example code would be most helpful. Thanks in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event | Excel Discussion (Misc queries) | |||
Change event Macro | Excel Discussion (Misc queries) | |||
Change event? | Excel Discussion (Misc queries) | |||
Change of Row event | Excel Discussion (Misc queries) | |||
change event/after update event?? | Excel Programming |