Public Sub for Userform Change Events

[Neuk]

SITUATION: I have a userform (A_MAIN) that displays a few command buttons. If the user presses the command button "Add" an additional userform (B_ADD) populates or if the user presses the command button "Modify" a different userform (C_MODIFY) populates. While each userform serves a separate and distinct function, they nevertheless share one thing in common - they all use four combo boxes (CB_FUNC, CB_COMP, CB_BLN, CB_FLN) and a textbox (TB_KEY) in the exact same way. That is, the user selects an option from a given combobox and the "key" lookup value generates in TB_KEY. With these look up values, I can find anything in my data tables (see the "parts" tab).

PROBLEM: As it stands now, I copied and pasted the change events and initialization routines into each userform. While that works fine, it creates a lot of redundant coding (it’s now in three different places). I would like to centralize the code into one place. Please see the attached file to see more (NOTE: I got rid of any additional coding that is not directly involved in this problem in the attached file, I hope that helps).

ATTEMPTED SOLUTION: I created a module and tried to create a public sub for the initalization and each change event to call. It doesn't work the way I intended…any help would be greatly appreciated. The redundant coding, used in each userform, is as follows:

Please Login or Register  to view this content.

[Bernie Deitrick]

Combine your second and third userforms into one, and hide/unhide the controls as needed depending on the choice made on the first userform, along the lines of:

Please Login or Register  to view this content.

You can also pass the userform object to your subroutines, and use that object instead of Me or a named userform.

Please Login or Register  to view this content.

....

[Tsjallie]

You could make a sub (in some Module) like this:

Please Login or Register  to view this content.

Then call that sub from the userforms_initialize procedures with

Please Login or Register  to view this content.

[Tsjallie]

As a matter of fact the Initialize-procedure can be shorter still.

Please Login or Register  to view this content.

Calling it remains the same.

[Neuk]

Combine your second and third userforms into one, and hide/unhide the controls as needed depending on the choice made on the first userform, along the lines of:

Please Login or Register  to view this content.

You can also pass the userform object to your subroutines, and use that object instead of Me or a named userform.

Please Login or Register  to view this content.

....

Bernie,
After thinking about it for a while, I think combining userform 2 and 3 will be very doable for me. I'll have to play around with it for a little bit, but I'm sure that will be the best way. Thank you for the idea. As far as using the same code for each of the comboboxes (CB_FUNC, CB_COMP, etc.) between userform 1 and 2 (MAIN, and now ADD/MODIFY) it looks like you define uForm as an object that can be used the same way when running either userform because the only thing that is different between userform 1 and 2, when it comes to the combo boxes, is the userform name. Hopefully I understood that correctly. I'll have to spend some time tomorrow playing around with it.

[Neuk]

As a matter of fact the Initialize-procedure can be shorter still.

Please Login or Register  to view this content.

Calling it remains the same.

Thanks Tsjallie for doing this. I'll have to play around with this tomorrow. While the initialize routine works well, do I simply follow the same pattern with a ComboBox change routine since the only thing that is different between the code is the userform name? Thanks for all the help!

[Tsjallie]

With some minor changes (variable declarations) you can have the same concept for handling change events for the comboboxes.
Add this sub to Module1:

Please Login or Register  to view this content.

Call the sub from the change event procedures with this line:

Please Login or Register  to view this content.

Substitute combobox name with the objectname of the combobox.

[Neuk]

Thanks Tsjallie! I think it is much cleaner.

[Tsjallie]

Glad I could help. Thx for the rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.