PDA

View Full Version : Solved: Edit Textbox after Format



Henryhbb
12-29-2005, 05:33 PM
I have an excel userform with about 50 or so textboxes on it for the user to enter numbers into. These textboxes are linked to a spreadsheet using "Control Source". They are also grouped into their own class so I can work with them collectively. When the userform is activated the textboxes are formated through the class module as follows:

Public Sub textgroup_Change()
textgroup.Value = Format(textgroup.Value, "#,##0.00")
End Sub

This works fine. When the userform loads the textboxes load the values from the spreadsheet and formats them as above. The problem occurs when the user tries to update one of the values. It only allows the user to input numbers right of the decimal.:dunno

I've tried everything I can think of to fix this. If anyone has encountered this problem before and found a solution I would greatly appreciate the help.

Thanks

Henry

tpoynton
12-29-2005, 08:06 PM
how are the values formatted in the spreadsheet itself? if people are going to modify the spreadsheet entries through userform, I would think that you would want them to modify the entries as they appear on the spreadsheet. you could format the cells on the spreadsheet before loading them into the userform. that way, you would not have to reformat them inside the userform, which seems to be causing the issues. ounce of prevention, pound of cure...

Henryhbb
12-29-2005, 08:28 PM
The spreadsheet is actually already formatted that way. That would be great if I could load in the value and the format. Is there some way to make the userform do that?

Thanks for your help,

Henry

tpoynton
12-29-2005, 08:40 PM
It would be helpful if you could post a sample workbook...when i use the following code in the userform_activate section of code, i get an exact duplicate of what is contained in the worksheet

textboxname = cells(1, 1).value

posting a sample workbook, if possible, would allow more people to try and help you solve the issue. if that is not possible, posting more of the VBA code would be helpful! i dont know what a "control source" is, but might if i saw it! people in this forum are particularly good at troubleshooting code - giving them something to go on will give you a good response.

tpoynton
12-30-2005, 06:56 AM
OK, this seems to work for me without issues...
TextBox.Value = Cells(2, 2).Value 'or however you get your data

TextBox.Value = (Format(TextBox, "0.00"))

I think your problem was tyring to do too much at once; by getting the value first (first line), then formatting it (second line), i think it will do what you want...

using your code, i think it might be:
textgroup.Value = 'however you populate textboxes
textgroup.value = Format(textgroup, "0.00")

the above code is in the userform_activate section, and should correctly populate the userform, allowing the user to modify the boxes as needed. I would probably then use a commandbutton to update the values in the worksheet instead of something like the onchange event. I supposed you could use the "afterupdate" event if you wanted changes in the sheet to be made right after the user moved to another part of the userform. hope this helps...tim

Henryhbb
12-30-2005, 11:26 PM
That fixed it. Thanks for your help

tpoynton
12-31-2005, 05:51 AM
Glad it worked! Please mark the thread as solved by going into "thread tools" (above your initial post)