Custom Cell Format problem

[Minitman]

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

 

[Guest]

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"

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" &
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

 

[Rick Rothstein \(MVP - VB\)]

Sorry Dave... this message was supposed to have been posted Minitman's
posting, not yours.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" &
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

 

[Minitman]

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

 

[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

 

[Dave Peterson]

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.

 

[Peter T]

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]

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

 

[Peter T]

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

 

[Peter T]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peter T]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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