dropdown list in alphabetical order data validation...
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: dropdown list in alphabetical order data validation...

  1. #11
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    This worked form me :

    Code assumes the DV cell is Cell B1 and the DV list is range A2==>to last populated cell in column A

    Place in the worksheet module:

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        Const DV_Cell As String = "B1" '<== Change as required.
        Const DV_LIST_HEADER As String = "A1" '<== Change as required.
    
        Dim oListRange As Range, oArrayList As Object
        Dim vListValues As Variant, vDVArrayList As Variant
        Dim i As Long
    
    
        If Target.Address = Range(DV_Cell).Address Then
            Set oListRange = Range(DV_LIST_HEADER, Range(Split(Range(DV_LIST_HEADER).Address, "$")(1) & Rows.Count).End(xlUp)).Offset(1)
            Set oArrayList = CreateObject("System.Collections.ArrayList")
            vListValues = oListRange.Value
            For i = LBound(vListValues, 1) To UBound(vListValues, 1)
                oArrayList.Add vListValues(i, 1)
            Next i
            oArrayList.Sort
            vDVArrayList = oArrayList.ToArray
            With Range(DV_Cell).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, ";")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    
    End Sub
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Jaafar Tribak View Post
    This worked form me :
    Hi Jaafar
    From a quick look I'm wondering if it is essentially any different to post 4?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #13
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Peter_SSs View Post
    Hi Jaafar
    From a quick look I'm wondering if it is essentially any different to post 4?
    I should have looked at that post more properly.. You are right. It is the essentially the same idea ... One difference is that the code is placed in the selection change event so it runs automatically.

    BTW, I forgot to change the excel List separator from ";" to ",".... To make it work regardless, I should have changed the following line:

    From:
    Code:
    .Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, ";")
    To :
    Code:
    .Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, Application.International(xlListSeparator))

    Thanks.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #14
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Peter_SSs View Post
    Does this comment relate to any of the suggestions made in this thread? If so, which one(s) & how does it relate to those posts?

    Otherwise
    1. Exactly where is your DV list?
    2. How did you derive the list?
    3. What is the actual Data Validation set-up in the cell?


    @ salim hasan
    Can you please look at the options like 'First Cell In Each Column' or 'User Defined Selection' etc when using the HTML Maker so you don't get those long lists of similar formulas or blanks like have been appearing in many of your posts.
    Hi.

    I am lost as to the above referred in red as i'm talking excel and not HTML?!.

    My data validation list comes from a set range "CustomerNames" on sheet Customers.

    The DV list is on cell T4 and it's setup as allow list and source being CustomerNames.

    Many thanks for your time.

    Cheers

    P.S: I now know what you were referring to. Thanks.
    Last edited by albertc30; Aug 24th, 2019 at 06:09 PM. Reason: forgot to mention...
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  5. #15
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by albertc30 View Post
    My data validation list comes from a set range "CustomerNames" on sheet Customers.

    The DV list is on cell T4 and it's setup as allow list and source being CustomerNames.
    You didn't answer my questions above the heading "Otherwise" in post 6 so I assume that all the extra lines (white as you referred to it) are from your own data validation list. Since you say the DV on cell T4 is set up with source CustomerNames, then you must have a lot of blank lines in that named range. Are you able to just remove those blank lines from the named range?



    Quote Originally Posted by albertc30 View Post
    P.S: I now know what you were referring to. Thanks.
    Last edited by Peter_SSs; Aug 24th, 2019 at 07:00 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #16
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    ops...

    Rookie mistake!

    My range is the entire column. That explains why!!!

    Thanks.

    Feeling like an utter imbecile now!!! lol...

    P.S; Answering your initial question, no it doesn't. Sorry.
    Last edited by albertc30; Aug 24th, 2019 at 07:20 PM.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by albertc30 View Post
    My range is the entire column. That explains why!!!
    It certainly does. Hopefully you have a more user-friendly list now!
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #18
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by Peter_SSs View Post
    It certainly does. Hopefully you have a more user-friendly list now!

    I most certainly do Peter.

    Much appreciated.

    It's the first time I have been playing with dynamic ranges. So, so much better and efficient.

    I'm still puzzled why the width of the list when it opens is wider that the cell it is in even though all names in it fit in the cell width with no issue at all.

    https://www.dropbox.com/s/p3v0ubd5zr...ot_13.png?dl=0

    Cheers.
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

  9. #19
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,339
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Quote Originally Posted by albertc30 View Post
    I'm still puzzled why the width of the list when it opens is wider that the cell it is in even though all names in it fit in the cell width with no issue at all.
    See if this article sheds any light on this issue. It does appear from your image that you may have some merged cells which is one of the issues mentioned in the linked article.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #20
    Board Regular albertc30's Avatar
    Join Date
    May 2012
    Location
    Norfolk
    Posts
    969
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: dropdown list in alphabetical order data validation...

    Hi all.

    I hope every one's having a good weekend.

    I am now loving it playing with dynamic rage lists.

    Having said that, I have now hit a brick wall.

    I am using the following code for the dynamic name range.

    =OFFSET(Products!$A$1,0,0,COUNTA(Products!$A:$A)+1,1)

    I have no headers on that.

    I am also using a comboBox active X control instead of using the cell rows. This is because the user wants to type say "H" and the list moves straight onto the beginning of items that start with the letter "H".

    This does work fine with my previous method of the entire column as the name range, which brings off course the unnecessary blank lines, thus not very user friendly as it was mentioned before.

    But having changed it to the new dynamic range, when I add a new product it just doesn't show up.

    I'm thinking this is because of either I am not using headers or something is not right on my code. Having said this, when I go to the name range and click the formula, it does show all as selected thus the new products as being part of said range.

    What am I doing wrong here please?

    Off course, if there's another easier and more productive way to achieve this I am all ears. Can the normal list on data validation someone allow user to type first letter and move straight to the first item on list that starts on said letter?

    As always, much appreciated for all of your help.

    Cheers
    Many thanks for all the help, much appreciated.

    Regards,
    Albert

    Reminder
    Remember to use code tags people.

Some videos you may like

User Tag List

Tags for this Thread

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
  •