Formatting numbers in a text box

[brucemc]

In a cell I might have 15 or 15.5.

In the text box that is NOT linked to the cell but gets it's value from
it in the activate function, I want it to display 15.00% or 15.50%.

I want the user to be able to change that number, and when they are
done, it to show to two decimals and the "%" sign.

Is there any direct formatting command, or am I going to "get" to go
through all kinds of gyrations of switching it to string, adding or
truncating charaters, displaying, checking for changes (if possible)
and reformatting for display?

Thank-you for all thoughts!

 

[Norman Jones]

Hi Bruce,

Try:

Me.TextBox1.Value = Format(Range("A1").Value / 100, "0.0%")

 

[brucemc]

Seemed like we were on the right path, and still suspect so, but
something awful weird is going on. You will notice in the following I
have stuck in a number of extra steps in that I am trying to disect the
problem:


Code:
--------------------
Private Sub tb_r_11_Change()
Dim dog
Dim cat
If TypeName(Me.tb_r_11.Value) = "String" Then
dog = Val(Me.tb_r_11.Value)
cat = dog / 100
Me.tb_r_11.Value = Format(cat, "0.00%")
Else
Me.tb_r_11.Value = Format(Me.tb_r_11.Value/100, "0.00%")
End If
End Sub
--------------------


What is happening is that at first the proper figure is displayed - let
us say 17.00%, which is drawn from a cell formatted as % whose
underlying value is .17, just in case that comes into play in the
problem and I don't know it - I do find it strange though that instead
of bringing in .17 as a number, it is bringing it over as a formattted
string, and the formatted string is "17" (a whole number - not a
decimal).

Then when I change it over to 17.5 in the text box on the form a change
event is again triggered and I get a type mismatch error. I checked out
what Me.tb_r_11.Value is equal to, and more importantly it IS a string,
being "17.50%", however and nonetheless, Excel is barfing it back to me
claiming a mismatch this time when I try to get the Val of it, but it
would not be running that section if it were not a string which is how
it was run the first time when bringing it in from the cell, so what
the heck???

 

[brucemc]

OK, my friends, I have "solved" the problem, I hope, for now, b
throwing in an On Error Resume, but I seriously think I am eithe
sticking a band-aid on my own error in coding that I just don't kno
enough to spot, or a stupid bug in Excel. Anyone able to help out o
this one