Number formatting in forms

[Brian]

I have a form that allows users to input data into cells in various
worksheets. When requested the form takes data from the cells it will write
to if the user chooses as "first value". What I would like to do is format
the text boxes so that the numbers taken from the spreadsheet are presented a
certain way ( one decimal place or as a % etc). Can I do this?

 

[Incidental]

Hi Brian

The following code is straight out of the excel vba help file, it
should give you a clear idea of how to do what you are after.

' User-defined formats.
MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".


I hope this helps you out

Steve

 

[Brian]

Not really ( or at least I can't see how it does). Might need more
explanation about what I've got...

Example from my code:

Mix_Constraints_Form.comp1_min.Value = Worksheets("Results").Cells(17,
7).Value

This sets the value in the form equal to the value in the cell of the
worksheet referenced and the user can enter a value into the form and
overwrite back to the worksheet if chosen.

This works fine, but the values taken from the worksheet are displayed in
the form unformatted. (for example in the worksheet the value from above
could be displayed as 49% but the form might show .494213871 value from the
worksheet when I want to display 49% in the form as well).

 

[Incidental]

Hi Brian

Sorry for the late reply, the code below is shows the value .494213871
as 49% using the format function.

With Mix_Constraints_Form

..comp1_min.Value = Format(Worksheets("Results") _
..Cells(17, 7).Value, "0%")

End With

You can change the format to reflect what you want. I'm not exactly
sure what exactly your aim is with this but i hope you see where i'm
coming from with this. You said in your first post you wanted to show
as one decimal place as well, excel rounds .494213871 up to 0.5 if you
format the cell to one decimal place. You can do that in code like so

With Mix_Constraints_Form

..comp1_min.Value = Format(Worksheets("Results") _
..Cells(17, 7).Value, "0.0")

End With

I hope this is more clear for you

Steve

 

[Brian]

That is clearer. Thanks for the help.

 

[prabodh.shetty]

That is clearer.  Thanks for the help.















- Show quoted text -

Try this... Instead of using cell.value use cell.text that would help
you.

Prabodh Shetty