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. |
On change event
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 "raj" wrote in message ... 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. |
On change event
You got a couple responses yesterday:
http://groups.google.com/groups?thre...5FF6%40msn.com (Are you punchy?) raj wrote: 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. -- Dave Peterson |
On change event
|
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com