Results 1 to 4 of 4

Thread: Drop Down List

  1. #1
    Member
    Join Date
    2007-04
    Posts
    17
    Login to Give a bone
    0

    Default Drop Down List

    Hello all,

    I have what I hope would be an easy question to answer. I created a userform that has a 2 drop down lists in it. The first drop down list is reserved for a group name. The second drop down list is reserved for specific descriptions depending on which group is selected. For example if I select group A it shows what set of descriptions and if I select group B it shows a different set of descriptions. Each description can only be used once.

    My question is this: Can I set it up so that when I select a group and use a description out of it it will not show that description the next time I select that same group? This will make it easier for my co-workers and I to complete our tasks with out trying to remember what has or hasn't been used.

  2. #2
    Active Member
    Join Date
    2007-12
    Posts
    68
    Login to Give a bone
    0

    Default Re: Drop Down List

    well, I think theres a few things to take into account here -

    firstly, am i correct in assuming that each time a description field is selected, then there will be one FEWER description field available in the second listbox, the next time?
    if so, you'll have to take into account WHEN to make this field not available - what if the wrong description is accidentally clicked on? can he go back? or is that description gone forever? You'd have to find a separate place in your code (like a save button, or something) as to when this description is officially gone.


    secondly, am i corect in assuming that these limits, etc, will be remembered every time the program is started baxck up? if so, now you are looking at storing this data in an eternal file. How much data? What kind if data? Where is this data being seeded from? These questions will help you choose which type of external file format to store to.

  3. #3
    Member
    Join Date
    2007-04
    Posts
    17
    Login to Give a bone
    0

    Default Re: Drop Down List

    Yes you are correct in your first assumption.

    As for your second assumption I don't need it to remember what it did the last time it was run. I will need it to pick back up from the beginning where it will have all the description available again.

  4. #4
    Login to Give a bone
    0

    Default Re: Drop Down List

    Sorry... I put in code mode now.

    Code:
    Option Explicit
    
    Private Sub UserForm_Initialize()
        With ComboBox2
            .AddItem "Name"
            .AddItem "Birthday"
        End With
    End Sub
    
    Private Sub ComboBox2_Change()
        
        'if combo is empty, exit sub
        ComboBox1.Clear
        If ComboBox2 = vbNullString Then Exit Sub
            
        Const rHeaderOffset As Long = 2 'So we have a row offset on
                                        'all sheets of the workbook
        
        'Let's suppose that your data is in columns
        Const cDataName     As String = "A"
        Const cDataBirthday As String = "B"
        '...in the sheet:
        Const str_wsData As String = "Data"
        
        'Let's suppose that you populate your data on the following columns...
        Const cOutputName      As String = "A"
        Const cOutputBirthday  As String = "B"
        '...in the sheet:
        Const str_wsOutput As String = "Report"
        
        Dim rLastData   As Long 'used for finding last row
        Dim rLastOutput As Long 'used for finding last row in another sheet
        Dim rng         As Range
        Dim rngEntries  As Range
        Dim rngSearch   As Range
        Dim cUsedData   As String
        Dim cUsedOutput As String
        Dim wsData      As Worksheet
        Dim wsOutput    As Worksheet
        
        'no error handling for the case of selecting a value other
        'than "Name" or "Birthday", OK?
        'Let's determine what which column will we compare: Name or Birthday?
        'What is the last row of the Names column?
        Select Case ComboBox2
            Case "Name"
                cUsedData = cDataName
                cUsedOutput = cOutputName
            Case "Birthday"
                cUsedData = cDataBirthday
                cUsedOutput = cOutputBirthday
        End Select
        
        Set wsData = Sheets(str_wsData)
        Set wsOutput = Sheets(str_wsOutput)
        
        'Before we add data on our combobox, we need to test if it is already used.
        'So, we need to test EACH entry that should enter.
        'Let's determine which is the last row so we can loop through the column:
        rLastData = wsData.Cells(wsData.Rows.Count, cUsedData).End(xlUp).Row
        
        'What's the last line where it exists entries?
        rLastOutput = wsOutput.Cells(wsOutput.Rows.Count, cUsedData).End(xlUp).Row
        
        'Before adding entries to combobox, we need to check if it doesn't exists in THIS range:
        Set rngEntries = wsOutput.Range(wsOutput.Cells(rHeaderOffset, cUsedData), wsOutput.Cells(rLastData, cUsedData))
        
        For Each rng In wsData.Range(wsData.Cells(rHeaderOffset, cUsedData), wsData.Cells(rLastData, cUsedData))
            Set rngSearch = rngEntries.Find(rng)
            If Not rngSearch Is Nothing Then
            'OK, this means that found a match... so this entry will not be added
            Else
                '...else, we will add the item!
                ComboBox1.AddItem rng
            End If
        Next rng
        
        'Let's release all the memory we used on these objects:
        Set rngEntries = Nothing
        Set rngSearch = Nothing
        Set wsData = Nothing
        Set wsOutput = Nothing
    
    End Sub

Similar Threads

  1. drop down list
    By cparvez in forum Parts Builder
    Replies: 2
    Last Post: 2012-08-05, 01:18 AM
  2. Drop Down list
    By cparvez in forum Revit MEP - Families
    Replies: 5
    Last Post: 2012-07-30, 08:09 PM
  3. CTB unselectable from drop-down list
    By matt.worland in forum AutoCAD General
    Replies: 10
    Last Post: 2006-06-23, 11:40 PM
  4. Drop down list for fields
    By autocad.wishlist1734 in forum AutoCAD Wish List
    Replies: 0
    Last Post: 2006-02-21, 05:19 PM
  5. Drop down layer list takes FOREVER to drop!
    By b_v_mc in forum AutoCAD General
    Replies: 12
    Last Post: 2005-12-05, 02:50 AM

Posting Permissions

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