Code to control data entry in Combo-box situated in userform

Hi.
Below is a code situated in a combobox in my userform.
The object of the code is that if the user enters anything but a number or the word "LISTED" then a message box should pop up and then clear the combobox.
the last part of the code does the following: once i enter a number in the box the code should automatically insert right after the number a value of " .00%", so if the user enters a 50 in the box the code should automatically turn it into a 50.00%
The trouble i believe is that the code is thinking that this addition of " .00%" is part of the instance of being a non-numeric value and the message box therefore gets displayed

Please Login or Register  to view this content.

Any help on this would be greatly appreciated.

Hi kosherboy,

Your main problem was that the code was running in an endless loop each time 'PriceBoxVend()' changed the value (by adding the '%' sign).

I made the following significant changes to the code:
a. I added the Global Boolean variable (with module Scope) 'bGblInhibitUserFormEvents'. The variable is used to stop 'PriceBoxVend_Change()' from recognizing changes to the ComboBox value while you are inside Sub PriceBoxVend_Change(). We need this variable because there is no UserForm equivalent of Application.EnableEvents.
b. When you added the .00% sign, you also added a blank in between the number and the decimal point. That caused the value to be INVALID. I removed the extra SPACE character.
c. I used variable sValue and xValue to be temporary copies of the ComboBox value. I did this because it was easier for me to read, and because it stopped unnecessary Change Events from being generated.
d. Other changes were made just because it is my programming style, and is easier for me to understand the code when it is written in my style.

Please Login or Register  to view this content.

I hope this helps.

Lewis

Hi Lewis,
Thanks for taking your time on my problem.
Small issue, when i added your code to take the place of my old code one of my variables bugged out. I'm not sure what is causing the issue but here is a small summary of what i have:
This code runs when the userform is called:

Please Login or Register  to view this content.

Not sure if this would help to understand my situation but i use a public variable for the following (this way i can call it anywhere in my project:

Please Login or Register  to view this content.

Thanks

Lewis,
I closed my workbook and tried your code again and it worked somewhat.
The code lets me enter 1 digit but when i enter a second digit like "55" the msg box popped up and the combobox displayed this "5.00%5" instead of "55.00%".
Also, i would like the code to be flexible and allow decimal points as well.

Thanks again.

Originally Posted by protonLeah

[CODE]Option Explicit

Hi protonleah,
Thanks for responding.
Your code still had the msg box pop up when i tried entering a number.

I'm not sure if this would be helpful but i use the same idea, mentioned in my first post, to actual cells via data validation.
Here is my data validation formula:

Please Login or Register  to view this content.

So this restricts me to letting me only enter a number (which will turn to a percentage) or the word LISTED and in this case also the word N/A.

I appreciate the rep points, but I think the thanks are a little bit premature.

The code lets me enter 1 digit...

The Change Event is not very friendly. It processes one keystroke at a time. If you're patient, and wait until the % comes up, you can then enter another number wherever you want, and you will eventually get the number you want.

--------------

An alternative is the Exit event, which lets you type as many characters as you want and only processes when the control loses focus. Of the two, I prefer the Change Event.

For software I write, I don't do data checking until I am ready to process everything. When I click on a CommandButton to process, data is checked before CommandButton processing is allowed to continue.

---------------------

For what i't worth, here is working Exit event code. Change the name of the Change event code (put an 'x' in front of the name), when testing the Exit event code. The two don't play nicely together.

Please Login or Register  to view this content.

Lewis

Originally Posted by LJMetzger

I appreciate the rep points, but I think the thanks are a little bit premature.

Lewis

Hi Lewis.
I gave you the rep because i appreciate the time you took in trying to solve my problem.
P.S. rep points can be given out to every response and doesn't cost me anything

In reference to your latest code, it works great! I don't, however, understand why you have to have everything updated by the command button. Because let's say a user is entering lots of data and some of that data does not compile with the program and when he presses the command button at the end 15 msg boxes pop up showing everything he did wrong when he could have corrected it, or have the software correct it while he was entering the data.
Also, in my case i need the userform to update as the user goes along so that the userform will be userfreindly
One last thing, if i stick to your code are there any issues that might occur?

Anyway, thanks for the code, i really appreciate that you took your time in working on the code and explaining your work to me. Your knowledge in vba is also very much appreciated.

Hi Lewis,
It was very generous of you to take your time in preparing and uploading the file, i understand it was time consuming and i appreciate it very much. I reviewed the file over the weekend and i see now the difference between the different options. This file will be great for future reference.

Best regards,

kosherboy