Solved

Format text boxes on Excel form

Posted on 2011-09-02
9
274 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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