Populating comboboxes and ranges through a class module

**Mats Samson** · 12-06-2011, 04:07 AM

I have 12 comboboxes where the user type text information or inserting ready written textlines from a list in the combobox. The information is passed via variables to the respective range of the comboboxes. There is also a possibility to “read” in previous data to the underlying combobox ranges, accordingly I’d like this information to appear in the comboboxes.

I like to eliminate the 12 combobox _Change and _AfterUpdate procedures using a class module keeping track of which combobox is handled!

Would be grateful for any assistance!
Regards
Mats

**romperstomper** · 12-06-2011, 05:06 AM

If you want specific help, I think we would need a sample workbook to work with.

**mikerickson** · 12-06-2011, 05:23 AM

Variables in a Class Module don't support an AfterUpdate event.
Code like this would make a common code for ComboBox_Change and AfterUpdate.
In a class module, named Class1

Please Login or Register  to view this content.

and in the UserForm's code module

Please Login or Register  to view this content.

Note that the common ComboBox_Change code is in the Class module, but the common ComboUpdate code is in the userform.

**Mats Samson** · 12-06-2011, 06:25 AM

Hello,
enclosing a workbook with an extraction of the code connected to the textline comboboxes
Best regards
Mats

**snb** · 12-06-2011, 07:46 AM

cfr. the attachment

Basically for comboboxes:
you need:

in the userform:

Please Login or Register  to view this content.

in the classmodule:

Please Login or Register  to view this content.

**Mats Samson** · 12-06-2011, 09:05 AM

Hello snb,
do you really mean that these few lines are the only thing needed to control the different events with the comboboxes!
Or is it so that the procedures you wrote only will be replacing the combobox Change event, everything else has to be kept as is?!??!
Please explain

Best regards
Mats

**romperstomper** · 12-06-2011, 09:12 AM

That only handles the Change events. As Mike said, the After_Update event is not exposed to a WithEvents variable, so you need to work around that.

**snb** · 12-06-2011, 09:36 AM

If you tell us what has to be 'done' using the comboboxes we can help you assess which eventhandles you need and how to code them in the classmodule.

**Mats Samson** · 12-06-2011, 02:39 PM

It's pretty much what I wrote in the initial quastion. The comboboxes are a part of creating or editing purchase, order and invoice documents.
The comboboxes are handling the additional textinformation that would be a part of the document, typical packing, delivery and transport information.
The comboboxes helds also a small list of pre-written text lines, eg. text lines often used in any document and by selecting a pre-defined text,it appears in the document. The user is also able to write his own text information.
When creating a purchase or order the connected text line ranges are saved to a separate "database" together with the other business information.
When it comes to delivery, the order will be invoiced by reading in the previous data (a simple copy to an excel sheet, the "system"-sheet), the information will be edited reflecting the particular delivery and then converted to an invoice that likewise will be saved to the database. There are also possibilities to read in the previous purchase/order/invoices and update the information if some conditions are changed. Copying back previous registrations is the reason why I have the Userform_Activate procedure as I'm leaving (hiding) the userform while picking up the database workbook where I select which order line to read back.

**Mats Samson** · 12-08-2011, 01:14 PM

Hello all!
It was solved elegantly through applying the proposal from snb! I even removed the _AfterUpdate procedures and still it looks to be working fine.
The _AfterUpdate was really a relic since long time back when I faced poor performance and jumpy updating of the combobox_change. Since I rewrote and
shrunk a great deal of the code everything runs very smooth and the textline comboboxes was actually one of the last, remaining details to improve!
Thanks for the good advise!