• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Format text boxes on Excel form

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
Sandra Smith
Asked:
Sandra Smith
  • 5
  • 4
1 Solution
 
andrewssd3Commented:
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
 
Sandra SmithRetiredAuthor Commented:
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
 
andrewssd3Commented:
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 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Sandra SmithRetiredAuthor Commented:
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
 
andrewssd3Commented:
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
 
Sandra SmithRetiredAuthor Commented:
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
 
andrewssd3Commented:
No problem - just post it when you're ready and I'll take a look.
0
 
Sandra SmithRetiredAuthor Commented:
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
 
Sandra SmithRetiredAuthor Commented:
Sorry it took so long, but actually a variation of this worked.

Sandra
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now