+ Reply to Thread
Results 1 to 4 of 4

VBA - Build ListBox based on multiple worksheets

  1. #1
    Registered User
    Join Date
    07-24-2009
    Location
    minnesota, usa
    MS-Off Ver
    Excel 2002
    Posts
    10

    VBA - Build ListBox based on multiple worksheets

    Problem
    So I have run into a bit of an issue. I am building a database in Excel (sigh) because my company does not support Access. I have most of it done using VBA but am running into two key problems.

    How do I populate a listbox showing the relationship between two sets of data?
    How can I use an edit function to create new relationships between those sets of data?

    If there is a better way to do this that does not utilize the relationship matrix I would be open to it as well.

    Database Structure
    Userform - frmUseCase has several functions:

    1. Allows review of existing Use Case information. Should also include information on which Business Rules are related to the Use Case being viewed. This second part is where the problem lies.
    2. Allows new record entry and editing. This is functional except for tying Business Rules to the new Use Case.

    Sheet "Use Case Header" - Contains most of the information including the unique Use Case ID.
    Sheet "Business Rules" - Contains individual business rules including the unique Business Rule ID.
    Sheet "Relationship Matrix" - Contains the Use Case ID's in column A from A2 down Business Rule ID's in row 1 from B1 over. Where a Use Case and Business Rule relate, an "X" is placed.

    Anticipated Outcome
    Basically, what I am thinking should happen is that a ListBox on the frmUseCase should:
    1) Identify the row where UseCaseID on frmUseCase occurs in column A of Sheet "Relationship Matrix"
    2) Identify which columns in the matching row contain an "X"
    3) Complie a list of column headers (BusinessRuleID's) where the match occurs
    4) Locate rows with matching BusinessRuleID's in Sheet "Business Rules"
    5) Display each BusinessRuleID and corresponding row in the userform ListBox as an entry.

    Example ListBox
    BR1 Description
    BR3 Description
    BR4 Description
    BR5 Description
    BR8 Description
    BR9 Description

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA - Build ListBox based on multiple worksheets

    Hello there,

    Is there any way that you could provide a sample workbook that mimics what you actual workbook does and what you are attempting to do?

    Thanks!

    RVASQUEZ

  3. #3
    Registered User
    Join Date
    07-24-2009
    Location
    minnesota, usa
    MS-Off Ver
    Excel 2002
    Posts
    10

    Re: VBA - Build ListBox based on multiple worksheets

    Sure, here is a mock database. The button is on the first sheet. There are also some functions not yet set up(i.e. Find) but the basics are there.

    After opening the form, the last box should populate with the following based on which BR's are tied to UC3.

    BR2 Test Busines Rule 2
    BR3 Test Busines Rule 3
    BR4 Test Busines Rule 4
    BR5 Test Busines Rule 5
    BR8 Test Busines Rule 8
    BR9 Test Busines Rule 9
    BR10 Test Busines Rule 10
    BR11 Test Busines Rule 11
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-24-2009
    Location
    minnesota, usa
    MS-Off Ver
    Excel 2002
    Posts
    10

    Re: VBA - Build ListBox based on multiple worksheets

    Anyone able to help?

+ 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