|
Custom Cell Format problem
Greetings,
I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman |
Custom Cell Format problem
Hi
I'm not 100% sure, but you could try: (assuming it's cell A1): If Range("A1").Value < Range("A1").Text Then MsgBox "Formatting applied" -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Minitman" wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman |
Custom Cell Format problem
If your userform code already takes care of the formatting, then you could just
stop the worksheet_change event from firing when your userform code runs. .... application.enableevents = false worksheets("somesheet").range("somerange").value = "some value here" application.enableevents = true Minitman wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman -- Dave Peterson |
Custom Cell Format problem
Hey Wigi & Dave,
Thanks for the fast replies., Wigi: I'm not sure if that will work, but the more I look at it I beginning to think it might. I'll give it a try. Dave: I hadn't thought of using the Application.EnableEvents in the UserForm. Someone else helped me on this code three years ago (I keep all of the newsgroup archives since 2003). Here is the sheet Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error GoTo EndIt Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" Application.EnableEvents = True Exit Sub EndIt: Application.EnableEvents = True End Sub It seems that the file I recovered from my hard drive crash doesn't have any code in the UserForm code section for TextBoxes. I remember coding two textboxes (raw data & formatted data). The raw data was transferred to the sheet (to be formatted by the sheet change code). I also remember that I could not change the value in the formatted TextBox, only the raw TextBox. The formatted data was only for display on the UserForm. This worked except when I tried to change the contents of any cell in that column later. That is why I am looking for a different direction to take this procedure. I'll try your two suggestion and see if I can figure out how to get it to work. I'll let you know if it works and how I got it to work or I'll be back looking for more help. Again, thanks to both of you. -Minitman On Sun, 8 Jul 2007 12:28:01 -0700, Wigi wrote: Hi I'm not 100% sure, but you could try: (assuming it's cell A1): If Range("A1").Value < Range("A1").Text Then MsgBox "Formatting applied" On Sun, 08 Jul 2007 14:43:03 -0500, Dave Peterson wrote: If your userform code already takes care of the formatting, then you could just stop the worksheet_change event from firing when your userform code runs. ... application.enableevents = false worksheets("somesheet").range("somerange").valu e = "some value here" application.enableevents = true Minitman wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman |
Custom Cell Format problem
I'm not sure why you couldn't change the "raw" textbox--but it seems to me that
you could either pass it a string to be formatted via the worksheet_change event or pass it an already formatted string (with events disabled). Minitman wrote: Hey Wigi & Dave, Thanks for the fast replies., Wigi: I'm not sure if that will work, but the more I look at it I beginning to think it might. I'll give it a try. Dave: I hadn't thought of using the Application.EnableEvents in the UserForm. Someone else helped me on this code three years ago (I keep all of the newsgroup archives since 2003). Here is the sheet Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error GoTo EndIt Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" Application.EnableEvents = True Exit Sub EndIt: Application.EnableEvents = True End Sub It seems that the file I recovered from my hard drive crash doesn't have any code in the UserForm code section for TextBoxes. I remember coding two textboxes (raw data & formatted data). The raw data was transferred to the sheet (to be formatted by the sheet change code). I also remember that I could not change the value in the formatted TextBox, only the raw TextBox. The formatted data was only for display on the UserForm. This worked except when I tried to change the contents of any cell in that column later. That is why I am looking for a different direction to take this procedure. I'll try your two suggestion and see if I can figure out how to get it to work. I'll let you know if it works and how I got it to work or I'll be back looking for more help. Again, thanks to both of you. -Minitman On Sun, 8 Jul 2007 12:28:01 -0700, Wigi wrote: Hi I'm not 100% sure, but you could try: (assuming it's cell A1): If Range("A1").Value < Range("A1").Text Then MsgBox "Formatting applied" On Sun, 08 Jul 2007 14:43:03 -0500, Dave Peterson wrote: If your userform code already takes care of the formatting, then you could just stop the worksheet_change event from firing when your userform code runs. ... application.enableevents = false worksheets("somesheet").range("somerange").valu e = "some value here" application.enableevents = true Minitman wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman -- Dave Peterson |
Custom Cell Format problem
Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" &
UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Sorry Dave... this message was supposed to have been posted Minitman's
posting, not yours. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" &
UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Hey Dave,
I am having trouble with this "raw" TextBox. When I load the UserForm. the "raw" TextBox receives the value from the formatted cell. This is not changed as it is loaded into the TextBox. It is when I paste the contents of the UserForm into a new row on the sheet that I get the mangled data (but only if the data going up was formatted). if there is no data going into the TextBox, there is no problem coming down to the sheet. If I add new data into the Textbox in the raw format, again there is no problem. This problem only occurs when I upload a current record to modify and then download it as a new record (This is done quite a bit). If I can get the code to tell if the value in the TextBox is formatted or not, I could then turn the EnableEvents on or off. Is there any way to check for this formatting? Any suggestions are appreciated. TIA -Minitman On Sun, 08 Jul 2007 18:19:59 -0500, Dave Peterson wrote: I'm not sure why you couldn't change the "raw" textbox--but it seems to me that you could either pass it a string to be formatted via the worksheet_change event or pass it an already formatted string (with events disabled). Minitman wrote: Hey Wigi & Dave, Thanks for the fast replies., Wigi: I'm not sure if that will work, but the more I look at it I beginning to think it might. I'll give it a try. Dave: I hadn't thought of using the Application.EnableEvents in the UserForm. Someone else helped me on this code three years ago (I keep all of the newsgroup archives since 2003). Here is the sheet Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error GoTo EndIt Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" Application.EnableEvents = True Exit Sub EndIt: Application.EnableEvents = True End Sub It seems that the file I recovered from my hard drive crash doesn't have any code in the UserForm code section for TextBoxes. I remember coding two textboxes (raw data & formatted data). The raw data was transferred to the sheet (to be formatted by the sheet change code). I also remember that I could not change the value in the formatted TextBox, only the raw TextBox. The formatted data was only for display on the UserForm. This worked except when I tried to change the contents of any cell in that column later. That is why I am looking for a different direction to take this procedure. I'll try your two suggestion and see if I can figure out how to get it to work. I'll let you know if it works and how I got it to work or I'll be back looking for more help. Again, thanks to both of you. -Minitman On Sun, 8 Jul 2007 12:28:01 -0700, Wigi wrote: Hi I'm not 100% sure, but you could try: (assuming it's cell A1): If Range("A1").Value < Range("A1").Text Then MsgBox "Formatting applied" On Sun, 08 Jul 2007 14:43:03 -0500, Dave Peterson wrote: If your userform code already takes care of the formatting, then you could just stop the worksheet_change event from firing when your userform code runs. ... application.enableevents = false worksheets("somesheet").range("somerange").valu e = "some value here" application.enableevents = true Minitman wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman |
Custom Cell Format problem
Hey Rick,
Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
You could always inspect the value in the textbox to see if the first 4
characters are "Map ", then check the next 4 characters to see if they're valid (all alpha-numeric???), then check for " <", and so forth. Minitman wrote: Hey Dave, I am having trouble with this "raw" TextBox. When I load the UserForm. the "raw" TextBox receives the value from the formatted cell. This is not changed as it is loaded into the TextBox. It is when I paste the contents of the UserForm into a new row on the sheet that I get the mangled data (but only if the data going up was formatted). if there is no data going into the TextBox, there is no problem coming down to the sheet. If I add new data into the Textbox in the raw format, again there is no problem. This problem only occurs when I upload a current record to modify and then download it as a new record (This is done quite a bit). If I can get the code to tell if the value in the TextBox is formatted or not, I could then turn the EnableEvents on or off. Is there any way to check for this formatting? Any suggestions are appreciated. TIA -Minitman On Sun, 08 Jul 2007 18:19:59 -0500, Dave Peterson wrote: I'm not sure why you couldn't change the "raw" textbox--but it seems to me that you could either pass it a string to be formatted via the worksheet_change event or pass it an already formatted string (with events disabled). Minitman wrote: Hey Wigi & Dave, Thanks for the fast replies., Wigi: I'm not sure if that will work, but the more I look at it I beginning to think it might. I'll give it a try. Dave: I hadn't thought of using the Application.EnableEvents in the UserForm. Someone else helped me on this code three years ago (I keep all of the newsgroup archives since 2003). Here is the sheet Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub If Target.Value = "" Then Exit Sub On Error GoTo EndIt Application.EnableEvents = False Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" Application.EnableEvents = True Exit Sub EndIt: Application.EnableEvents = True End Sub It seems that the file I recovered from my hard drive crash doesn't have any code in the UserForm code section for TextBoxes. I remember coding two textboxes (raw data & formatted data). The raw data was transferred to the sheet (to be formatted by the sheet change code). I also remember that I could not change the value in the formatted TextBox, only the raw TextBox. The formatted data was only for display on the UserForm. This worked except when I tried to change the contents of any cell in that column later. That is why I am looking for a different direction to take this procedure. I'll try your two suggestion and see if I can figure out how to get it to work. I'll let you know if it works and how I got it to work or I'll be back looking for more help. Again, thanks to both of you. -Minitman On Sun, 8 Jul 2007 12:28:01 -0700, Wigi wrote: Hi I'm not 100% sure, but you could try: (assuming it's cell A1): If Range("A1").Value < Range("A1").Text Then MsgBox "Formatting applied" On Sun, 08 Jul 2007 14:43:03 -0500, Dave Peterson wrote: If your userform code already takes care of the formatting, then you could just stop the worksheet_change event from firing when your userform code runs. ... application.enableevents = false worksheets("somesheet").range("somerange").valu e = "some value here" application.enableevents = true Minitman wrote: Greetings, I have raw data in the format of 3 numbers, 3 letters and 2 numbers (eg.123abc45). This is either coming from a Userform or directly typed in from by the user. The final format should be "Map 123A <BC-45". I have tried worksheet change event. This works with a problem, coming off of the UserForm this data is already in this format and the worksheet change event adds the formatting on top of the formatted dated giving this as the result "Map MAP <12-5" instead of "Map 123A <BC-45". Is there any way to see a custom format without it actually changing the data? Any help is greatly appreciated. -Minitman -- Dave Peterson |
Custom Cell Format problem
If I follow, you only want to reformat cell's string if necessary. Following
attempts to combine your Target criteria with Rick's voodoo - Private Sub Worksheet_Change(ByVal Target As Range) Dim bFlag As Boolean Dim s1 As String, s2 As String If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub s1 = Target.Value If Len(s1) = 0 Then Exit Sub s2 = Replace(LCase(s1), "map", "") s2 = Replace(s2, "<", "") s2 = Replace(s2, "", "") s2 = Replace(s2, " ", "") s2 = Replace(s2, "-", "") s2 = Format(s2, "!Map @@@@ \<@@-@@\") bFlag = s1 < s2 If bFlag Then On Error GoTo EndIt Application.EnableEvents = False Target.Value = s2 End If EndIt: If bFlag Then Application.EnableEvents = True End If End Sub Regards, Peter T "Minitman" wrote in message ... Hey Rick, Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Hey Peter,
Sorry it took so long to get bask to you. I had to add a column to adjust 3000 numbers. Just finished and eliminated that extra column. Now I was able to test your modification to Rick's voodoo (how does that work, anyway?) That works great. Thank you very much. -Minitman On Tue, 10 Jul 2007 11:27:18 +0100, "Peter T" <peter_t@discussions wrote: If I follow, you only want to reformat cell's string if necessary. Following attempts to combine your Target criteria with Rick's voodoo - Private Sub Worksheet_Change(ByVal Target As Range) Dim bFlag As Boolean Dim s1 As String, s2 As String If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub s1 = Target.Value If Len(s1) = 0 Then Exit Sub s2 = Replace(LCase(s1), "map", "") s2 = Replace(s2, "<", "") s2 = Replace(s2, "", "") s2 = Replace(s2, " ", "") s2 = Replace(s2, "-", "") s2 = Format(s2, "!Map @@@@ \<@@-@@\") bFlag = s1 < s2 If bFlag Then On Error GoTo EndIt Application.EnableEvents = False Target.Value = s2 End If EndIt: If bFlag Then Application.EnableEvents = True End If End Sub Regards, Peter T "Minitman" wrote in message .. . Hey Rick, Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
...Rick's voodoo - how does that work, anyway?
I'd rather Rick answered this, but as it's been a while he may no longer be following the thread. So here goes - debug.? format("123abc45", "!Map @@@@ \<@@-@@\") ' Map 123A <BC-45 The @'s represent characters in the input string as text The first "" is a UD format control character, converts the input string to uppercase ("<" lower) The ! is also a UD format, controls if characters will fill the @'s from left or from right. The sample expects 6 six characters, try with more and less than 6 and w/out the !. The \'s escape any control characters so they can be included as text. Other characters, such as Map, space's & the "-" get included providing the format string includes at least one control character, or they could be escaped with \'s to get included. Regards, Peter T "Minitman" wrote in message ... Hey Peter, Sorry it took so long to get bask to you. I had to add a column to adjust 3000 numbers. Just finished and eliminated that extra column. Now I was able to test your modification to Rick's voodoo (how does that work, anyway?) That works great. Thank you very much. -Minitman On Tue, 10 Jul 2007 11:27:18 +0100, "Peter T" <peter_t@discussions wrote: If I follow, you only want to reformat cell's string if necessary. Following attempts to combine your Target criteria with Rick's voodoo - Private Sub Worksheet_Change(ByVal Target As Range) Dim bFlag As Boolean Dim s1 As String, s2 As String If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub s1 = Target.Value If Len(s1) = 0 Then Exit Sub s2 = Replace(LCase(s1), "map", "") s2 = Replace(s2, "<", "") s2 = Replace(s2, "", "") s2 = Replace(s2, " ", "") s2 = Replace(s2, "-", "") s2 = Format(s2, "!Map @@@@ \<@@-@@\") bFlag = s1 < s2 If bFlag Then On Error GoTo EndIt Application.EnableEvents = False Target.Value = s2 End If EndIt: If bFlag Then Application.EnableEvents = True End If End Sub Regards, Peter T "Minitman" wrote in message .. . Hey Rick, Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Typo -
The sample expects 6 six characters, try with more and less than 6 and w/out the !. that should of course be 8 characters etc. Also forgot to add, could also use "!Map @@@@ \<@@-@@\" as a custom number format. Although that works if 123abc45 is entered (manually or from your userform), if user is not aware and types in say 'Map' etc it could cause more confusion in an over helpful kind of way. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... ...Rick's voodoo - how does that work, anyway? I'd rather Rick answered this, but as it's been a while he may no longer be following the thread. So here goes - debug.? format("123abc45", "!Map @@@@ \<@@-@@\") ' Map 123A <BC-45 The @'s represent characters in the input string as text The first "" is a UD format control character, converts the input string to uppercase ("<" lower) The ! is also a UD format, controls if characters will fill the @'s from left or from right. The sample expects 6 six characters, try with more and less than 6 and w/out the !. The \'s escape any control characters so they can be included as text. Other characters, such as Map, space's & the "-" get included providing the format string includes at least one control character, or they could be escaped with \'s to get included. Regards, Peter T "Minitman" wrote in message ... Hey Peter, Sorry it took so long to get bask to you. I had to add a column to adjust 3000 numbers. Just finished and eliminated that extra column. Now I was able to test your modification to Rick's voodoo (how does that work, anyway?) That works great. Thank you very much. -Minitman On Tue, 10 Jul 2007 11:27:18 +0100, "Peter T" <peter_t@discussions wrote: If I follow, you only want to reformat cell's string if necessary. Following attempts to combine your Target criteria with Rick's voodoo - Private Sub Worksheet_Change(ByVal Target As Range) Dim bFlag As Boolean Dim s1 As String, s2 As String If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub s1 = Target.Value If Len(s1) = 0 Then Exit Sub s2 = Replace(LCase(s1), "map", "") s2 = Replace(s2, "<", "") s2 = Replace(s2, "", "") s2 = Replace(s2, " ", "") s2 = Replace(s2, "-", "") s2 = Format(s2, "!Map @@@@ \<@@-@@\") bFlag = s1 < s2 If bFlag Then On Error GoTo EndIt Application.EnableEvents = False Target.Value = s2 End If EndIt: If bFlag Then Application.EnableEvents = True End If End Sub Regards, Peter T "Minitman" wrote in message .. . Hey Rick, Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
I'd rather Rick answered this, but as it's been a while he may no longer
be following the thread. So here goes - Why? Your explanation was right on the mark Rick debug.? format("123abc45", "!Map @@@@ \<@@-@@\") ' Map 123A <BC-45 The @'s represent characters in the input string as text The first "" is a UD format control character, converts the input string to uppercase ("<" lower) The ! is also a UD format, controls if characters will fill the @'s from left or from right. The sample expects 6 six characters, try with more and less than 6 and w/out the !. The \'s escape any control characters so they can be included as text. Other characters, such as Map, space's & the "-" get included providing the format string includes at least one control character, or they could be escaped with \'s to get included. Regards, Peter T "Minitman" wrote in message ... Hey Peter, Sorry it took so long to get bask to you. I had to add a column to adjust 3000 numbers. Just finished and eliminated that extra column. Now I was able to test your modification to Rick's voodoo (how does that work, anyway?) That works great. Thank you very much. -Minitman On Tue, 10 Jul 2007 11:27:18 +0100, "Peter T" <peter_t@discussions wrote: If I follow, you only want to reformat cell's string if necessary. Following attempts to combine your Target criteria with Rick's voodoo - Private Sub Worksheet_Change(ByVal Target As Range) Dim bFlag As Boolean Dim s1 As String, s2 As String If Target.Count 1 Then Exit Sub If Target.Column < 47 Then Exit Sub s1 = Target.Value If Len(s1) = 0 Then Exit Sub s2 = Replace(LCase(s1), "map", "") s2 = Replace(s2, "<", "") s2 = Replace(s2, "", "") s2 = Replace(s2, " ", "") s2 = Replace(s2, "-", "") s2 = Format(s2, "!Map @@@@ \<@@-@@\") bFlag = s1 < s2 If bFlag Then On Error GoTo EndIt Application.EnableEvents = False Target.Value = s2 End If EndIt: If bFlag Then Application.EnableEvents = True End If End Sub Regards, Peter T "Minitman" wrote in message .. . Hey Rick, Thanks for your reply, I like your solution, it is nice and clean. The old approach does get the job done in a clunky sort of way. However, neither approach fixes the problem of sheet to UserForm to sheet conversion and data mangling that results. (See my reply to Dave's reply) Any ideas? -Minitman On Sun, 8 Jul 2007 21:27:08 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & "" This is going to seem like magic<g... Your above line of code can be replaced with this... Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\") Rick |
Custom Cell Format problem
Also forgot to add, could also use "!Map @@@@ \<@@-@@\"
as a custom number format. I don't thinks so... a spreadsheet cell's format patterns are different than those that are valid for the VBA Format function. Rick |
Custom Cell Format problem
"Rick Rothstein (MVP - VB)" wrote in
Also forgot to add, could also use "!Map @@@@ \<@@-@@\" as a custom number format. I don't thinks so... a spreadsheet cell's format patterns are different than those that are valid for the VBA Format function. Rick Er, obviously not, in any case custom number-format only formats numbers! When I tested it apparently worked. Absent mindedly I forgot the worksheet change event, as posted previously, was there and kicking in ! Regards, Peter T |
Custom Cell Format problem
Also forgot to add, could also use "!Map @@@@ \<@@-@@\"
as a custom number format. I don't thinks so... a spreadsheet cell's format patterns are different than those that are valid for the VBA Format function. Rick Er, obviously not, in any case custom number-format only formats numbers! Not entirely true... you can do a limited amount of text formating (but nothing anywhere near what VBA's Format function can do). Put this custom format string... <<@ in the Custom Number-Format and anything entered in that cell will be surrounded by double angle brackets. An even more complex example would be this custom format string #;(#);Z\E\RO!;W\h\at i\s \"@\"\? which will print positive numbers with a leading plus sign, negative numbers surround by parentheses instead of having a leading minus sign, zero will be spelled out and, for any non-number, you will be asked what is it. When I tested it apparently worked. Absent mindedly I forgot the worksheet change event, as posted previously, was there and kicking in ! Don't you just **love** those kind of flub-ups? Rick |
Custom Cell Format problem
Not entirely true... you can do a limited amount of text formating (but
nothing anywhere near what VBA's Format function can do). To expand slightly on this... the limitation in a cell's Custom Number Format is that the @ stands for the entire contents of the cell... you can't get to the individual characters of the cell content in order to do neater things like is possible in VBA. Rick |
Custom Cell Format problem
"Rick Rothstein (MVP - VB)" wrote i
...in any case custom number-format only formats numbers! Not entirely true... you can do a limited amount of text formating (but nothing anywhere near what VBA's Format function can do). Put this custom format string... <<@ in the Custom Number-Format and anything entered in that cell will be surrounded by double angle brackets. An even more complex example would be this custom format string #;(#);Z\E\RO!;W\h\at i\s \"@\"\? which will print positive numbers with a leading plus sign, negative numbers surround by parentheses instead of having a leading minus sign, zero will be spelled out and, for any non-number, you will be asked what is it. First magic and now plain trickery, what next <g When I tested it apparently worked. Absent mindedly I forgot the worksheet change event, as posted previously, was there and kicking in ! Don't you just **love** those kind of flub-ups? Can't live without them, unfortunately ! Regards, Peter T PS, in next post the limitation in a cell's Custom Number Format is that the @ stands for the entire contents of the cell Indeed, shame |
Custom Cell Format problem
#;(#);Z\E\RO!;W\h\at i\s \"@\"\?
which will print positive numbers with a leading plus sign, negative numbers surround by parentheses instead of having a leading minus sign, zero will be spelled out and, for any non-number, you will be asked what is it. First magic and now plain trickery, what next <g See what you can learn to do by reading Harry Potter books.<g Just to clarify, the plus sign was accidentally omitted when I copied the format pattern string from my spreadsheet to the posting I sent. The above format pattern string should have been this one... +#;(#);Z\E\RO!;W\h\at i\s \"@\"\? so that the positive numbers would have the leading plus sign I said they would. Rick |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com