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

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

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

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

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

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

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

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

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

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

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

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