Combobox
Combobox
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Combobox

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello
    I am trying to create a combobox that has a list of data in a column that elimate the duplicated data and sort them.
    I have a code(from the website) that creates very similar function I need. The only differences is that it uses listbox and not combobox. Then I try to modify it to combobox but I can't get it to work
    The following is the code for the web:
    Sub SortAndRemoveDupes()
    Dim rListSort As Range, rOldList As Range
    Dim strRowSource As String

    'Clear Hidden sheet Column A ready for list
    Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear


    'Set range variable to list we want
    Set rOldList = Sheet2.Range("A1", Sheet2.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:=Sheet1.Cells(1, 1), Unique:=True

    'Set range variable to the new non dupe list
    Set rListSort = Sheet1.Range("A1", Sheet1.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 = Sheet1.Name & "!" & Sheet1.Range _
    ("A2", Sheet1.Range("A65536").End(xlUp)).Address

    Sheet1.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 is the code after I modified:

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

    'Clear Hidden sheet Column A ready for list



    'Set range variable to list we want
    Set rOldList = Columns(2)

    'Use AdvancedFilter to copy the list to Column A _
    of the hidden sheet and remove all dupes
    rOldList.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Cells(1, 2), Unique:=True

    'Set range variable to the new non dupe list
    Set rListSort = Columns(2)

    With rListSort
    'Sort the new non dupe list
    .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With

    'Parse the address of the sorted unique items
    strRowSource = Columns(2).Address
    MsgBox "StrRowSource" & strRowSource

    With UserForm1.txtWeight

    'Clear old ListBox RowSource
    .RowSource = vbNullString
    'Parse new one
    .RowSource = strRowSource
    '.AddItem = strRowSource

    End With

    End Sub


    Can someone please help

    Thanks in advance

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the problem you are having?

    Also, did you name your combo box txtWeight?

    [ This Message was edited by: Russell Hauf on 2002-03-26 15:12 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, I did name my combobox as txtWeight
    I got an error on the following line
    Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    I can't figure out what cause this problem
    Please help
    Thanks in advance

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Allen

    Did you download the example that goes with this?

    You have made modifications to the code, but not allowed for this in other parts of the code! For example you have:

    'Set range variable to list we want
    Set rOldList = Columns(2)

    'Use AdvancedFilter to copy the list to Column A _
    of the hidden sheet and remove all dupes
    rOldList.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Cells(1, 2), Unique:=True

    I doubt this would work as you are trying to tell Advanced filter to Copy uniques to another range, but 'the range' is the range of the original list!



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-26 18:17 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Dave or Anyone
    Is the line below

    'Set range variable to list we want
    Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))

    the same as the line below?

    Set rOldList = Columns(1)

    Thanks in advance

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello
    Can anyone tell me howcome the line below works
    'Clear Hidden sheet Column A ready for list
    Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear

    and the line below doesn't work. Aren't they the same???


    Worksheets("Hidden").Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear


    Thanks

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Allen,

    Will you go through my example file..

    16. Populating Combobox

    here it has shown how to populate the comboboxes secondly to your code which removes duplicate..

    Okay..

    will you try my code and make necessay modifications.

    you can get code for deleting duplicates or say extracting unique values from my website...

    file nos is 5 ( it is a simple example) and other file which can interest you is file nos
    22.


    I hope if you study this two file will solve your problem

    if you still have difficulty then write back to me

    http://www.pexcel.com/download.htm

    ni****h desai
    http://www.pexcel.com

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello nisht
    can you give me the VBA code passward for the file #5,16,22
    Thanks

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Allen,
    please write me on

    nisht@pexcel.com

    i will sent you password.

    and it is free

    ni****h desai

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hello Nisht
    can you send me the password now
    sorry I don't mean to rush you. I am just trying to finish this application before my boss come this afternoon
    Thanks in advance

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com