+ Reply to Thread
Results 1 to 10 of 10

Populating comboboxes and ranges through a class module

  1. #1
    Registered User
    Join Date
    07-09-2010
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    26

    Populating comboboxes and ranges through a class module

    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
    Last edited by Mats Samson; 12-08-2011 at 01:19 PM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Populating comboboxes and ranges through a class module

    If you want specific help, I think we would need a sample workbook to work with.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Populating comboboxes and ranges through a class module

    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.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    07-09-2010
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Populating comboboxes and ranges through a class module

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

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Populating comboboxes and ranges through a class module

    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.
    Attached Files Attached Files
    Last edited by snb; 12-06-2011 at 07:52 AM.



  6. #6
    Registered User
    Join Date
    07-09-2010
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Populating comboboxes and ranges through a class module

    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

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Populating comboboxes and ranges through a class module

    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.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Populating comboboxes and ranges through a class module

    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.

  9. #9
    Registered User
    Join Date
    07-09-2010
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Populating comboboxes and ranges through a class module

    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.

  10. #10
    Registered User
    Join Date
    07-09-2010
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Populating comboboxes and ranges through a class module

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1