Results 1 to 14 of 14

Userform ComboBox populating Comboboxes from worksheet data

Threaded View

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Manitoba
    MS-Off Ver
    2016
    Posts
    36

    Userform ComboBox populating Comboboxes from worksheet data

    What I am trying to accomplish is having the agent name populate cbAgentName combobox based on cbMU as well as populating cbAgent combobox. Both these comboxes should act the same as cbSkName and lbMSN. If in cbMU I choose 100 then cbAgentName and cbAgent should populate with all agents that are in the group # 100 based on columns A:C
    Also I need the lbData to populate as well but only with the agents that have skillnames and levels that do not match
    So summary is lbData should show agents name, skill name, skill level sys1 and sys2 only if the skill levels don’t match but I want the data to be pulled from the columns A:G based on what they should retrieve and the lbData should stay blank till cbMU chosen then display one instance of each agent with mismatched skills. the cbAgent and cbAgentName can be filled like what the way lbMSN is setup but again one instance of each agent not multiples like on the worksheet.
    Some background:
    Listbox (lbMSN) & ComboBox (cbSkName) rely on what a person chooses under ComboBox (cbMU)

    Worksheet GtoICheck has columns and rows that I need to populate Listbox (lbData) ComboBox (cbAgentName)
    All columns are static and cannot be changed as the empty columns seen do have data that I had to remove.
    The names of agents are dynamic so the range can vary even as deep as from A2:H1500
    Columns R:T are the Sys1 reference and are the accurate point of reference
    Column F pulls data for the agent but from a different worksheet
    Column G is formula based to look for agent in Column A and pull that agents skill Level from another sheet but for this sample I placed the values in.
    The colored cells (normally are conditional formatted) but just colored to reflect what is green is accurate and what is red is not. So level1 9 in Sys1 is =Level1 in Sys2 thus cell is green. (9=1, 5=5 and 0=0 are all accurate) but 9=5, 5=0, 0=9, 0=5 are not accurate thus they are red or would be in the conditional format formula

    What I did so far is add an array in cbMU so when 100 is chosen it displays the skill names in lbMSN and cbSkName. You will also notice the skill level in lbMSN is also displaying but if you take a look at the array it is really time consuming to try and align as what I did. If there is a way to improve on the code I used I look forward to it as I have over 40 skills, skill names and levels that are needed to be added.
    Columns R:T have the Sys1 MU’s, Skill names and levels and ideally should feed the cbMU results in lbMSN and cbSkName instead of using the array
    I really need help on the first initial problem for the cbAgentName and cbAgent to populate the way I need it too. The array would be a bonus and accepted with total gratitude as well.

    To give an idea for the array used and my apologies for ugly it looks
    Private Sub cbMU_Change()
    Dim idx As Long
    Dim arr As Variant
    Dim isa As Long
    Dim rra As Variant
    idx = cbMU.ListIndex
    If idx = -1 Then Exit Sub ' nothing selected, so exit
    Select Case cbMU.Value
    Case "100"
    arr = Array("Skill1 1", "Skill2 1", "Skill3 5", "Skill4 1", "Skill5 1", "Skill6 5")
    Case "112"
    arr = Array("Skill9 1", "Skill15 5", "Skill16 5", "Skill17 1")
    End Select
    lbMSN.List = arr
    isa = cbMU.ListIndex
    If isa = -1 Then Exit Sub ' nothing selected, so exit
    Select Case cbSkName.Value
    Case "100"
    rra = Array("Skill1", "Skill12", "Skill3", "Skill4", "Skill5", "Skill6")
    Case "112"
    rra = Array("Skill9", "Skill15", "Skill16", "Skill17")
    End Select
    
    
    cbSkName.List = arr
    
    End Sub
    Any help is greatly appreciated.
    Attached Files Attached Files
    Last edited by bbqqsmokeman; 08-20-2017 at 08:20 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Populating userform textboxes basis of results from 2 userform comboboxes
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-11-2017, 10:26 AM
  2. [SOLVED] Filter worksheet data using 4 dependant comboboxes and populate in 'ListBox' on a userform
    By p_nayak268 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-27-2014, 12:41 PM
  3. Populating Userform ComboBox Using Items from Content Control Combobox
    By anarxo in forum Word Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2014, 01:00 PM
  4. If multiple comboboxes not used then stop populating cells at last combobox input
    By JacobBlue in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2013, 07:42 AM
  5. [SOLVED] Populating UserForm ComboBoxes with sheet entries
    By GalmOne in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-14-2012, 08:26 AM
  6. Populating comboboxes in a userform
    By metametrics in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2011, 09:34 AM
  7. [SOLVED] Populating Userform ComboBox with worksheet names in XL2003
    By MAWII in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-09-2005, 05:05 PM

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