Code in userform to be flexible and work with multiple textboxes

Hello forum!
Below is a code that works with a textbox named PriceBox1 in my userform. The issue is that there are about 20 textboxes that need to use the same code. How could i set up the below code to be flexible and work with the "active textbox"? Otherwise i would have to copy the below code 20 times.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ok, so i did some searching and found a code that works with controls that are within a frame.

Below is my modified code which indeed uses the ctrl that is within the frame as the active control but it's not perfect. For example, if i enter a value other than LISTED or a number and i switch controls then the code thinks that the active control is the one in question when in essence it was the first control that was activated.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Create a Custom Class and include a local TextBox object using Dim WithEvents. Because you declared it WithEvents you will see it in the left hand drop down in Vbe and you will be able to include and event handler for your Exit event. In the event handler you can call your sTB1_Code passing the class module TextBox as a parameter.
You can then create an instance of this class and connect its TextBox property to your active TextBox.
Alternatively, you could make a loop to create a Collection of Objects, all instances of your custom Class, each referencing a TextBox, so that you have one for each TextBox. If the Collection is declared as Public in a standard module, it will remain active and one of the collection members will fire each time you get an Exit Event on a TextBox.
The key words are Class Module, Dim WithEvents and Collection. You could also Google VBA Control Array.



Please read the manual: http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

Ok, so here is a sample workbook Set textboxes as range.xlsm
Please note the blue font below in code:

Please Login or Register  to view this content.

Actually there is a bit of a problem with the method I suggested and the reason is because of the Enter and Exit events being unavailable to MSForms.TextBox Objects declared outside of a Form Module (in a Class Module for example).

However, thanks to this thread, I have a work around. One way anyway to hack these events.

There are two Class Modules: one to wrap the control and another to monitor the Active control in the Form and Raise events when this changes (a pair of Exit/Enter events). The events pass the Active Control or the Previously Active Control as a parameter.

Please find attached...

Hi kosherboy, the only one to worry about is Set textboxes as range CB 5.xlsm... please ignore the others: they are only there coz I can't figure out how to delete attachments.

Not sure what you mean about the bottom-most control; it always starts with the top one in focus for me.

The functionality you are trying to add needs to be in the sTB1_Code routine. Just be aware that it will be the same for all controls.
The constraints about not leaving blanks is also due to your code in sTB1_Code. That routine is the behaviour that occurs after an Exit event, so just adjust it to what you need.

Hi coolblue,
When i open the userform it opens on my second screen and has the cursor blinking on the top most cell, when i drag it to the screen where excel is displayed the cursor moves to the bottom. I am having trouble figuring this issue out and also how to make the code give me the option of selecting any textbox.
Also, i switched activecell.offset(1,23) to mTarget and the code copied "LISTED" to the specified cell....HOW SIMPLE!

I tried incorporating your code into my own using active cell method but code sent compile error message: "constant expression required". The blue font is what i changed and the red is what got bugged:

Please Login or Register  to view this content.

Any help on this would be great.

Originally Posted by mikerickson

It sounds like you have two custom objects.
A clsCommonTextBox to drive the code for each of the tb's with common event code
and a clsEveryControl to track which control is Active and fire a pseudo-Enter or Exit event.

if clsEveryControl detects a change in ActiveControl
- If LastActive is common then fire pseudo-Exit through clsCommonTextBox
- If NowActive is common then fire pseudo-Enter through clsCommonTextBox

I should look at your code, (I suspect that LastActive_pseudoExit might fire after NowActive_(true)Enter).

Thanks for the idea.

Using your terminology, it works like this...
The UserForm maintains a Collection of clsEveryControl objects using the Name property of the wrapped Control as a Key. It can access any of these objects using the Name of the control.
The clsCommonTextBox object (of which there is only one) has a private routine which, when called, will check to see if the ActiveControl has changed.
All of the clsEveryControl objects (of which there are many) have a reference to the clsCommonTextBox object.
The clsEveryControl objects will call the controlClick method on the clsCommonTextBox object on their MouseUp or KeyUp events.
The controlClick method in the clsCommonTextBox will execute it's private routine that checks for any change in the ActiveControl and raise custom events accordingly. The custom events pass a reference to the source control to their subscribers.
The UserForm also has a reference to the clsCommonTextBox object and subscribes to it's events. It's event handlers can reference the source clsEveryControl object using the control name of the source control that is passed to it by the clsCommonTextBox object.

The Exit event is raised before the Enter event.

Originally Posted by kosherboy

I tried incorporating your code into my own using active cell method but code sent compile error message: "constant expression required". The blue font is what i changed and the red is what got bugged:

Please Login or Register  to view this content.

Any help on this would be great.

I don't recomend using the ActiveCell like that but if you must...

Please Login or Register  to view this content.

I'm not happy with the architecture of the previous versions so I am attaching another version which has much better encapsulation.
I moved all of the positioning and supervisory code back into the form and dramatically simplified the TextBox and Frame wrapper objects. The only functionality remaining in these objects now is that which is required for producing the Exit/Enter events. The cTextBox Class could easily be generalised by adding redundant variables of any control type that needs to be supported (and event handlers to match), along with a Select statement in the Control setter. The cFrameEvents class is also easily extended to support multiple control types.

kosherboy, with this version, you only need to modify code in the form (textBoxTargetRange and sTB1_Code).

ExitEnter 6.png

Thank you Coolblue for all your help and support. I really appreciate all the time you spent on this thread. I learned a lot from it and i'm very thankful to you for that.

Best regards,
Kosherboy