Solved

Combobox Additem

Posted on 2002-05-30
6
422 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Printing causes application to got into "not responding" 6 43
Word - Access 3 49
Excel If tests 10 64
Some AHK commands fail in Microsoft OneNote 5 35
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

20 Experts available now in Live!

Get 1:1 Help Now