Solved

Format text boxes on Excel form

Posted on 2011-09-02
9
271 Views
Last Modified: 2012-05-12
I have an Excel user form with five texboxes.  But I maybe just not seeing it, how to I format the control for number with commas (no decimals) and other fields with decimals for numbers like 0.90?
0
Comment
Question by:ssmith94015
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36476071
Excel VBA uses MSForms controls, which don't have as many attributes as modern controls.  You don't have a format or mask option, so you have to use something like the Format function in VBA to load the values in the first place.
0
 

Author Comment

by:ssmith94015
ID: 36476080
Then I am in a circle as the textbox is blank until the user enters a value, so it formats after the value is entered?  I am working in VBA, but the format functionality does not apply.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36476116
You have to check the value after the user enters it using one of the event handling procedures for the text box, either control_Change or control_BeforeUpdate.  Then if it validates you can format it and reload it into the textbox.  I don't know exactly what formatting you want, so it's difficult to give examples, but as a basic example:


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(TextBox1.Value) Then
        TextBox1.Value = Format(TextBox1.Value, "0.00")
    Else
        MsgBox "Invalid value"
        Cancel = True
    End If
End Sub

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 36476296
Sorry, now I get Object required error. I  give up, this has got to go online today and they will just have to deal with it the way it is.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36477469
OK - I'm not sure exactly what's going wrong.  Is it a userform, or textboxes on a worksheet.  It would probably be best if you could post your workbook if it's not too late and I'll try to help further.
0
 

Author Comment

by:ssmith94015
ID: 36477843
Andrewssd3, thanks for the offer.  This is proprietry information so I will have to alter some data.  It is on line but I would still like to solve the problem.  These are six text boxes on a user form created in the VBA development environment.  Each textbox's control source is a specific cell on the worksheet.  I will not be able to post it until Tuesday as the offices are closed until then.  Thanks for the offer of help as this is really perplexing me.  I work mostly in ACCESS so trying to do the same thing in Excel is becoming a challenge.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36477862
No problem - just post it when you're ready and I'll take a look.
0
 

Author Comment

by:ssmith94015
ID: 36529354
andrewssd3, I have not forgotten this but was pulled onto another project when the developer left and there was a lot to do for it to come online on schedule.  
0
 

Author Closing Comment

by:ssmith94015
ID: 36950631
Sorry it took so long, but actually a variation of this worked.

Sandra
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now