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
<(E-Mail Removed)> 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
<(E-Mail Removed)> 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").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

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
> <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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").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

>> 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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>>> 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

>> 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
<(E-Mail Removed)> 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
>> <(E-Mail Removed)> 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
>> <(E-Mail Removed)> 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").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

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\)"
<(E-Mail Removed)> 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