User form text box question

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
Is there a way to prevent someone from entering something into a text box on a user form that is not in a list? If I name the list can I enter that name some where in the properties to prevent it from being entered?

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi,

The Combobox is designed for this type of entry. You can set the MatchRequired property to true so that the user must type/select something from the list. And you can also set the ShowDropButtonWhen property to "Never" and then it will even look like a textbox.

HTH
DK
 
Upvote 0
DK,

Thanks that works well, one more question, is there a way to change the error message that pops up to state something else?

Thanks for your help.
 
Upvote 0
You could use VBA to validate it instead if you want a custom message. You need to turn the MatchRequired to false and then put this code into the userform's code module - change the ComboBox1 to the name of your combobox.

Code:
Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Me.ComboBox1.MatchFound = False Then
    MsgBox "Please choose from the list.", vbInformation, "Invalid Entry"
    Cancel = True
End If


End Sub

HTH
DK
 
Upvote 0
Ok I think I have done what you have requested but as soon as I start typing a number in the combo box it fills in with a correct number.
 
Upvote 0
Hi

You might have changed the MatchEntry property - you can set it to None, First Letter or Complete depending if you want the combobox to "autocomplete". If you don't want it to do that then set it to None.

HTH
DK
 
Upvote 0
I tried a letter instead of a number and it entered anyway, I must not have the code in the correct place. I have 7 modules and put it in the first open one I found.
 
Upvote 0
You need to put it in the code module that contains the combobox.

Easiest way is to right click the combobox on your userform and choose View Code. By Default it will create a procedure called Combobox1_Change. There should be two drop-down boxes at the top of the Visual Basic Editor - the one on the right says "Change" - change this to "BeforeUpdate" and then paste the code that I gave you before:

Code:
If Me.ComboBox1.MatchFound = False Then
    MsgBox "Please choose from the list.", vbInformation, "Invalid Entry"
    Cancel = True
End If

But you must make sure that the "ComboBox1" bit is changed to the same name as your actual combobox on your form. If you can't get it to work please post back your code as it currently stands.

Cheers
DK
 
Upvote 0
DK, Ok thanks for sticking with me here, it is kinda working except as soon as I type the first number the error code comes up.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top