dropdown list in alphabetical order data validation...

[albertc30]

Hi all, again...

I have been reading on this and have found a few good youtube videos.

One in particular mentioned 4 ways this could be achieved, the best way being the new function SORT.

Sadly, this is not available on my newly bought office 2019 professional. Just another way Microsoft has to push us in the direction they want us to go.

Another way to do it was by means of a query. To be honest I never have worked with queries and the one thing about it was that it needed refreshing manually?!

There's also, off course the macro. To use this I'd have to copy the column in question to another sheet as to have it sorted independently and only the data I'm interested in.

Is there a better simpler way to achieve this?

Any help is truly appreciated.

Thank you.
Cheers

 

[Jaafar Tribak]

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:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const DV_Cell As String = "B1" [B][COLOR=#008000]'<== Change as required.[/COLOR][/B]
    Const DV_LIST_HEADER As String = "A1" [B][COLOR=#008000]'<== Change as required.[/COLOR][/B]

    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

 

[Peter_SSs]

This worked form me :

Hi Jaafar
From a quick look I'm wondering if it is essentially any different to post 4?

 

[Jaafar Tribak]

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:

.Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, [B][COLOR=#ff0000]";"[/COLOR][/B])

To :

.Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, [COLOR=#ff0000][B]Application.International(xlListSeparator)[/B][/COLOR])

Thanks.

 

[albertc30]

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.

 

[Peter_SSs]

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?

P.S: I now know what you were referring to. Thanks.

 

[albertc30]

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.

 

[Peter_SSs]

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

It certainly does. Hopefully you have a more user-friendly list now!

 

[albertc30]

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/p3v0ubd5zrktbzi/Screenshot_13.png?dl=0

Cheers.

 

[Peter_SSs]

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.

 

[albertc30]

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