Use spreadsheet cell format to set userform text box format - how?

**barryleajo** · 08-20-2010, 06:51 AM

Hi Everyone
I have tried searching the forum but can't quite find a solution to my problem.

In the spreadsheet I have cells that hold values which represent 'targets'.
These can all be different and can change from time to time i.e. number, percent, string etc. depending on whether they are quantitative or qualitative targets.
The format of these are set to suit at setup or when the targets change - no problem

I can display the contents of these cells in a VBA userform using text boxes OK.

I need a way of detecting the cell number format in the spreadsheet to automatically set the format of the text box in the userform so that the values display correctly in the userform.

I don't want to 'hard code' the text box formats each time the target type change - there can up to 70 of them!

I have read up on the FORMAT function but it seems you need to know the format required first in order to use it.

Any help gratefully received?

WTIA

barry

**romperstomper** · 08-20-2010, 07:32 AM

Use the Text property of the range. For example:

Please Login or Register  to view this content.

will put the displayed value of A1 into the textbox.

**barryleajo** · 08-20-2010, 08:09 AM

Hi romperstomper
Many thanks for your quick response.

I can collect the value of the spreadsheet cell and put it into the text box OK, thanks.

It is the FORMAT of the cell I am trying to capture and apply to the text box.

Sorry if this wasn't clear

**romperstomper** · 08-20-2010, 08:14 AM

Let me try that again:

Originally Posted by romperstomper

Use the Text property of the range. For example:

Please Login or Register  to view this content.

will put the displayed value of A1 into the textbox.

**barryleajo** · 08-20-2010, 08:29 AM

ER...bit of a red face this end!!

Brilliant, so simple and yet so elegant.

Keep up the good work ... and your patinece levels!

Many thanks

I'll mark it as SOLVED now and tip your scales.

barry

**romperstomper** · 08-20-2010, 08:44 AM

Glad to help.

**romperstomper** · 08-20-2010, 08:44 AM

Glad to help.

**tim_chisman** · 11-08-2010, 06:32 AM

i can't get this to work...
Likewise, i want to return the value in cell A1 into a textbox,

**romperstomper** · 11-08-2010, 07:17 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.