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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
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,218
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,218
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,122,747
Messages
5,597,905
Members
414,189
Latest member
Duque_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top