User form text box question

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
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.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026

ADVERTISEMENT

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.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026

ADVERTISEMENT

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.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,026
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

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