Solved

Combobox Additem

Posted on 2002-05-30
6
419 Views
Last Modified: 2011-09-20
Hello, I have four worksheets. And i need to know how do i go abouts
adding certain items to a combobox on a userform. Bascially i need to
add the items on deet1 and deet 3 starting at a26 through a50
interval of 2 (ex. a26,a28, and so on.)Then for deet2 and deet4 starting
at a11 to a47 interval of 2.  Then here is the tricky part for me.
Some lines will have duplicate data. If it does how do i get it to
appear only once in the combobox. Thank you for any help.
0
Comment
Question by:LucyTaylor
6 Comments
 
LVL 3

Expert Comment

by:Elmo_
ID: 7046602
Lucy,

I am not one Hundred Percent as to what you are looking for here.

I take it that you want to load two different Combo Boxes with a List of Values.

These values are coming from Column A and you want all the Even (e.g. a26, a28, a30) Values from this column to go into the First Combo Box and All the Odd (e.g. a11, a13, a15) Values from this column to go into the Second Combo Box. In addition to this you do not want any duplicate Data in the combo Boxs.

What do you mean by "deet1 and Deet3"?  I think I have a piece of code which will do this for you, I would just like to be sure of what I am doing.

Cheers,

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7046613
Lucy,

I am not one Hundred Percent as to what you are looking for here.

I take it that you want to load two different Combo Boxes with a List of Values.

These values are coming from Column A and you want all the Even (e.g. a26, a28, a30) Values from this column to go into the First Combo Box and All the Odd (e.g. a11, a13, a15) Values from this column to go into the Second Combo Box. In addition to this you do not want any duplicate Data in the combo Boxs.

What do you mean by "deet1 and Deet3"?  I think I have a piece of code which will do this for you, I would just like to be sure of what I am doing.

Cheers,

Ed.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7047024
Lucy,

I was havnig a look at this again and I wrote a Macro that will do basically everything that you are asking for.  There are only one or two differences.

What this code does is it looks to a column of data in COL "A".  It only works off one worksheet (But can be easily modified for multiple sheets).  

What it does is it reads through the each row in the column.  The number of rows is specified by the START_ROW and END_ROW.

I have separated the code so that it will first read through the list and Load all the Data in the Even rows (While removing duplicates) and then Load all the Data in the Odd rows (While removing duplicates).

If you would like to test this, put two combo boxs on your worksheet and then past this list into the A col:

Hello
Ed
Salmon
How
Are
You
Ed
I
Are
Fine
Am
Ed

When the Macro runs it will ask for the Start and end Rows.  Supply them and the macro will load the Even Combo box.  It will then ask for them again and when supplied it will load the Odd combo box.

I separated the code so that it would be easy to modify for your self.  If you have problems with the code or need me to change it, please post exactly what you need.

Cheers,

Ed.

----------------------------


Sub Load_Data()

Const Col1 As String = "A"

Dim Cell1 As String

Dim Address1 As String

Dim Start_Row As Integer
Dim End_Row As Integer
Dim Count As Integer
Dim Count2 As Integer
Dim Num_elements As Integer
Dim Element_Present As Boolean

    Start_Row = CInt(InputBox("Please Enter the Start Row for Even Numbers!", "Start Row - Even Numbers"))
    End_Row = CInt(InputBox("Please Enter the End Row for Even Numbers!", "End Row - Even Numbers"))
   
    ActiveSheet.Shapes("Drop Down 1").Select
    With Selection
        .RemoveAllItems
    End With
   
    Count = Start_Row
    Cell1 = Col1 & Count
   
    For Count = Start_Row To End_Row
       
        If (Count Mod 2) = 0 Then
            ActiveSheet.Shapes("Drop Down 1").Select
           
            Num_elements = Selection.ListCount
            Element_Present = False
            For Count2 = 1 To Num_elements
                If Selection.List(Count2) = Trim(Range(Cell1).Value) Then
                    Element_Present = True
                End If
            Next Count2
           
            If Element_Present = False Then
                Selection.AddItem (Trim(Range(Cell1).Value))
            End If
           
        End If
       
        If Count < End_Row Then
            Cell1 = Col1 & Count + 1
        End If
       
    Next Count
   

    Start_Row = CInt(InputBox("Please Enter the Start Row for Odd Numbers!", "Start Row - Odd Numbers"))
    End_Row = CInt(InputBox("Please Enter the End Row for Odd Numbers!", "End Row - Odd Numbers"))


    ActiveSheet.Shapes("Drop Down 2").Select
    With Selection
        .RemoveAllItems
    End With
   
    Count = Start_Row
    Cell1 = Col1 & Count
   
    For Count = Start_Row To End_Row
       
        If (Count Mod 2) <> 0 Then
            ActiveSheet.Shapes("Drop Down 2").Select
           
            Num_elements = Selection.ListCount
            Element_Present = False
            For Count2 = 1 To Num_elements
                If Selection.List(Count2) = Trim(Range(Cell1).Value) Then
                    Element_Present = True
                End If
            Next Count2
           
            If Element_Present = False Then
                Selection.AddItem (Trim(Range(Cell1).Value))
            End If
           
        End If
       
        If Count < End_Row Then
            Cell1 = Col1 & Count + 1
        End If
       
    Next Count
End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Accepted Solution

by:
WJReid earned 100 total points
ID: 7051468
Hi LucyTaylor

I am assuming that you mean sheet rather than deet.

If you add two more sheets, sheet5 and sheet6 to your file you can use the following code to remove duplicates from the listbox and sort the list into alphabetical order. Sheet 5 can be hidden if you like:


Sub ShowTheForm()
UserForm1.Show
End Sub

Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String

 'Clear Hidden sheet Column A ready for list
  Sheet5.Range("A1", Sheet5.Range("A65536").End(xlUp)).Clear
 
 
 'Set range variable to list we want
  Set rOldList = Sheet6.Range("A1", Sheet6.Range("A65536").End(xlUp))

 'Use AdvancedFilter to copy the list to Column A _
  of the hidden sheet and remove all dupes
  rOldList.AdvancedFilter Action:=xlFilterCopy, _
               CopyToRange:=Sheet5.Cells(1, 1), Unique:=True
               
 'Set range variable to the new non dupe list
  Set rListSort = Sheet5.Range("A1", Sheet5.Range("A65536").End(xlUp))

    With rListSort
             'Sort the new non dupe list
             .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _
                   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
   
     'Parse the address of the sorted unique items
           strRowSource = Sheet5.Name & "!" & Sheet5.Range _
           ("A2", Sheet5.Range("A65536").End(xlUp)).Address
   
    Sheet5.Range("A1") = "New Sorted Unique List"
   With UserForm1.ListBox1
      'Clear old ListBox RowSource
      .RowSource = vbNullString
      'Parse new one
      .RowSource = strRowSource
  End With
 
End Sub

This code assumes that your form is called UserForm1 and that the list box is called ListBox1 on your form, you can adjust the code if you have called them something else.

And add this code behind your form :

Private Sub UserForm_Initialize()
Run "SortAndRemoveDupes"
End Sub

Private Sub UserForm_Terminate()
Sheet1.Columns(1).Clear
End Sub

This code will remove duplicates and sort the list into order.

Regards,

WJReid

0
 
LVL 44

Expert Comment

by:bruintje
ID: 7064241
Hi LucyTaylor, any update on this one? :O)Bruintje
0
 

Author Comment

by:LucyTaylor
ID: 7064985
Thank You, Your information helped me out a great deal
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now