On change event

  • Thread starter Thread starter raj
  • Start date Start date
R

raj

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.
 
Raj

I'm not sure you can do what you want. I don't think you can have a number
with 24 digits.

If for example, you enter 123456789012345678901234, a 24 digit number, Excel
will display it as 1.23456789012345E+23 in the formula bar and 1.234567E+23
in the cell. That's with format general. If you change it to numeric
format it then displays as 1.23456789012345E+23 in the formula bar and
1.23456789012345000000000.00 in the cell.

So I don't think you can have the format you want is the shorter answer.
Probably something to do with 15 digits precision.

Regards

Trevor
 
Back
Top