I've got a couple cells formatted as Special, then Social Security Number.
When users enter the spreadsheet they are presented with a series of VBA
forms for data entry, and on one of them they input a SSN. The idea is to let
them enter the ssn with or without dashes, and have the cell's formatting
apply the dashes if necessary.

However, when the number in in the form's text box is moved to the
spreadsheet's cell, the number is formatted exactly as the user places it
into the text box. The cell formatting is not applied, so if the user omits
the dashes, then the dashes don't appear in the cell.

I've checked the text box properties, both in the properties window and in
the .properties choices and don't see anything. Even tried populating the
cell with TextBox.Value rather than just TextBox, hoping it would work like
Paste Special/Values but that didn't work either.

If anyone can help with a way around this, much appreciated. Thank you.

Hard to comment without seeing some code but as a guess you are populating
the cell with text an not a number. Textboxes return text and not values. You
may need to coerce your output from the text box to a long (you can use cLng
function to do the conversion)...
--
HTH...

Jim Thomlinson


"danhattan" wrote:

I've got a couple cells formatted as Special, then Social Security Number.
When users enter the spreadsheet they are presented with a series of VBA
forms for data entry, and on one of them they input a SSN. The idea is to let
them enter the ssn with or without dashes, and have the cell's formatting
apply the dashes if necessary.

However, when the number in in the form's text box is moved to the
spreadsheet's cell, the number is formatted exactly as the user places it
into the text box. The cell formatting is not applied, so if the user omits
the dashes, then the dashes don't appear in the cell.

I've checked the text box properties, both in the properties window and in
the .properties choices and don't see anything. Even tried populating the
cell with TextBox.Value rather than just TextBox, hoping it would work like
Paste Special/Values but that didn't work either.

If anyone can help with a way around this, much appreciated. Thank you.

Here's the code I'm using:

Range("AppSSN").Select
ActiveCell.Value = frmAppInfo.boxAppSSN

and then alternatively
ActiveCell.Value = frmAppInfo.boxAppSSN.Value

I didn't consider that number formatting wouldn't apply to text, so thanks
for that heads up. Not familiar with the cLng function but I'll dig into it
and see what I can find.

Thanks again.


"Jim Thomlinson" wrote:

Hard to comment without seeing some code but as a guess you are populating
the cell with text an not a number. Textboxes return text and not values. You
may need to coerce your output from the text box to a long (you can use cLng
function to do the conversion)...
--
HTH...

Jim Thomlinson


"danhattan" wrote:

I've got a couple cells formatted as Special, then Social Security Number.
When users enter the spreadsheet they are presented with a series of VBA
forms for data entry, and on one of them they input a SSN. The idea is to let
them enter the ssn with or without dashes, and have the cell's formatting
apply the dashes if necessary.

However, when the number in in the form's text box is moved to the
spreadsheet's cell, the number is formatted exactly as the user places it
into the text box. The cell formatting is not applied, so if the user omits
the dashes, then the dashes don't appear in the cell.

I've checked the text box properties, both in the properties window and in
the .properties choices and don't see anything. Even tried populating the
cell with TextBox.Value rather than just TextBox, hoping it would work like
Paste Special/Values but that didn't work either.

If anyone can help with a way around this, much appreciated. Thank you.