+ Reply to Thread
Results 1 to 5 of 5

Consolidate repetitive code - (populating two comboboxes from an advanced filter)

  1. #1
    Registered User
    Join Date
    05-06-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    32

    Consolidate repetitive code - (populating two comboboxes from an advanced filter)

    Once again I want to learn how to simplify my code.

    I have a userform with two frames each containing two comboboxes.
    ( Frame1 > Category1 > Item1 and Frame2 > Category2 > Item2)

    In the BeforeUpdate event of both ‘Category’ comboboxes, I populate the corresponding ‘Item’ combobox from a filtered range.
    The code for each (see below) is almost identical. The exception being the numbers: 1 and 2.

    My goal is to write the code once only and then call it in the BeforeUpdate events, but I can’t figure out how to go about it.
    I have worked out if I ‘Dim i As Integer , i = 1’ then Me.Controls(“Category” & i).Value is the same as Me.Category1.Value, but I can’t get any further.
    It's not at all clear to me how to call a subroutine when there are variables involved.

    Any help appreciated.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    970

    Re: Consolidate repetitive code - (populating two comboboxes from an advanced filter)

    Edit: Wrong code

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    970

    Re: Consolidate repetitive code - (populating two comboboxes from an advanced filter)

    Untested, but maybe something like this:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-06-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2013
    Posts
    32

    Re: Consolidate repetitive code - (populating two comboboxes from an advanced filter)

    Fantastic, Akuini. Thank you! It works perfectly and has the simplicity I was hoping for.

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    970

    Re: Consolidate repetitive code - (populating two comboboxes from an advanced filter)

    You're welcome, glad to help, & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2021, 04:30 PM
  2. Replies: 3
    Last Post: 02-18-2015, 02:02 PM
  3. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  4. Consolidate Repetitive Data
    By arasan25 in forum Excel General
    Replies: 13
    Last Post: 02-13-2012, 05:18 AM
  5. Advanced filter code
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2011, 09:14 AM
  6. User Form with repetitive comboboxes
    By 00Able in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-22-2011, 03:08 PM
  7. Advanced Filter VBA code
    By Paul Thomson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2010, 04:18 AM

Tags for this Thread

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