Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserve cell formatting as SSN
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserve cell formatting as SSN
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserve cell formatting as SSN
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I concatenate a number and preserve the formatting? | Excel Worksheet Functions | |||
How to preserve formatting with vlookup | Excel Discussion (Misc queries) | |||
Preserve formatting in Pivot Chart | Charts and Charting in Excel | |||
Pivot Table - Preserve Formatting | Excel Worksheet Functions | |||
HOW DO I PRESERVE FORMATTING WHEN PASTING INTO A NEW WORKBOOK? | Excel Discussion (Misc queries) |