Put a "number" into a cell as a string 1

[Gav42]

I am trying to put the value that a user has entered in a combo box into a cell as a string using
....Formula = Str(ctrl.Value)
However the result is always a number (tested with the istext() function.

So how should I be doing it please?

 

[SkipVought]

hi,

With YourRange
   .NumberFormat = "@"
   .Value = Str(ctrl.Value)
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, there is no IsText in VB!

Any string value that contains a the text for a numeric value, will ecaluate TRUE for IsNumeric(). So your num ber stored as a string will appear as numeric.

What are you attempting to accomplish with this technique?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gav42]

That worked - thanks!
Confused though as this is part of a process that adds a new row into my data table and starts by copying the last row in the table and clearing the contents of cells whose values are to be taken from my userform.
My seed row contains numeric strings initially so why can't I simply clear contents and assign a different string?
The seed cell is general format as proved by =CELL("format",C158) returning G
So I guess I need to work out how I achieved that in the first place!

 

[SkipVought]

If you're using Excel 2007+, have you considered using Structured Tables? This might preclude having to copy "the last row in the table and clearing the contents of cells whose values are to be taken from my userform."


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gav42]

Some users are still on earlier versions, sadly. I seem to have actually solved my problem by pre-formatting the cells when the workbook is created. Not worked out how the numeric text gets in ok in the first place as that is part of a different process but problem solved (subject to backward compatibility checking)