MsgBox Not Exiting Sub as it Should.

[unit285]

So I have a Userform called ufMoral and a TextBox on the Userform called tb1 and I need to restrict the values for this Textbox to 3 letters only.
S, N, or U

Here is my code:

Please Login or Register  to view this content.

My problem is that when the Error MsgBox is triggered I have to click Ok on the MsgBox 3 times before it closes. I am open to any suggestions on how I can do this better or how I can fix this problem. All help is greatly appreciated!
Thanks!

[cytop]

Posting code like that without providing the context (How it's called/Event handler etc) is pointless... and there's no reason (in the code you have posted) that the MsgBox will be displayed 3 times.

As it seems that only 'S', 'N' or 'U' are valid entries, a ComboBox with those 3 options in the list and the style set to DropDownList (User cannot type anything, only select from the list) would get rid of a lot of issues for you.

[unit285]

Posting code like that without providing the context (How it's called/Event handler etc) is pointless... and there's no reason (in the code you have posted) that the MsgBox will be displayed 3 times.

As it seems that only 'S', 'N' or 'U' are valid entries, a ComboBox with those 3 options in the list and the style set to DropDownList (User cannot type anything, only select from the list) would get rid of a lot of issues for you.

Sorry, the code is located in the change event for tb1. I considered a listbox but for my own reasons i much prefer to stay with textbox if possible.
What i am trying to accomplish is when a user enters a forbidden value, (anything except S, N, or U) a msgbox should popup and warn the user that only those 3 values are allowed. When the user clicks ok on the msgbox it should disappear, tb1.text should be set to "" to remove the forbidden value, and then it should exit sub. However as soon as the msgbox comes up, you have to hit the ok button 3 times before the msgbox disappears and the exit sub happens.

[jaslake]

Hi unit285

As you may be aware, Application Enable Events Code doesn't work in UserForms so you need to create other Error Trapping Methods. Easily done. It's perhaps why the Change Event Code fires multiple times.

Not enough information to apply.

[Logit]

Here is a Select Case method. Hopefully you can apply it to your situation:

One userform, one textbox, one commandbutton. Change the control names as required.

Please Login or Register  to view this content.

[xlnitwit]

Hi

What are the reasons for using a text box over combo box as cytop suggested? They occupy the same real estate on screen and the combo can remove your issue.

[unit285]

Here is a Select Case method. Hopefully you can apply it to your situation:

One userform, one textbox, one commandbutton. Change the control names as required.

Please Login or Register  to view this content.

Thanks a lot for this response! As I was reading the code you posted an idea spawned which ended up solving my problem. The solution was to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.