Re: Custom Cell Format problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 ***
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 ***. 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
<petersod@xxxxxxxxxxxxxxxx> 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 *** 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 *** (to be formatted by the *** 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
<Wigi@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
<petersod@xxxxxxxxxxxxxxxx> 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 work*** change event. This works with a problem,
coming off of the UserForm this data is already in this format and the
work*** 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
.

Quantcast