Solved

Format text boxes on Excel form

Posted on 2011-09-02
9
272 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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