?
Solved

Format text boxes on Excel form

Posted on 2011-09-02
9
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

771 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