Formatting Text Box to control user input

Mkammari

Board Regular
Joined
Nov 23, 2005
Messages
65
I have a problem with Formatting text boxes that I'm racking my brain over.

I am trying to format the text boxes to allow only a five digit number for zip code and phone number and can't seem to get it to work. The answer may be simple but I can't seem to find it.

Here is the following code for the zip code. And if you could provide one for a phone number (###)-###-#### is the format I'm looking for , it would be greatly appreciated.

Code:
Private Sub Txtzip_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TxtZip.Value = WorksheetFunction.Text(TxtZip.Value, "00000")
End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
What do you actually want to do?

Do you want to validate the user input or format it?
Code:
Private Sub Txtzip_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
   
TxtZip.Value = Format(TxtZip.Value, "00000") 
End Sub
 

Mkammari

Board Regular
Joined
Nov 23, 2005
Messages
65
Formatting

Not familiar with validations.

The user will be inputting a value within the text box as he fills out the userform. I don't want the user to be able to put in more than 5 digits for the zip code...and no less or no more than 7 digits for the phone number.

The formatting will be done on a worksheet, so I'm not too concerned about this aspect right now. However, I will create another user form that will show all the data placed on a worksheet so the formatting may be necessary down the road.

I hope this makes sense.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
I still unsure what you are after.

Do you want to restrict the user to only entering a 5 digit number for the zip code? If they enter anything else what do you want to happen.
 

Mkammari

Board Regular
Joined
Nov 23, 2005
Messages
65

ADVERTISEMENT

My plan would be to have an error message stating that the number of digits is incorrect.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Well that's validation.
Code:
Private Sub Txtzip_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    
If Len(TxtZip.Value)<>5 Or Not IsNumeric(TxtZip.Value) Then
   MsgBox "You must enter a 5-digit number for the zip code"
   Cancel=True
End If

End Sub
 

Mkammari

Board Regular
Joined
Nov 23, 2005
Messages
65
That worked great. Thank you so much. So close but yet so far away.

Great forum. And I'll try and be a little more detailed from here on out.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,731
Messages
5,573,889
Members
412,555
Latest member
mark84
Top