dropdown list in alphabetical order data validation...

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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" [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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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, [B][COLOR=#ff0000]";"[/COLOR][/B])
To :
Code:
.Add Type:=xlValidateList, Formula1:=Join(vDVArrayList, [COLOR=#ff0000][B]Application.International(xlListSeparator)[/B][/COLOR])

Thanks.
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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.
:biggrin:
 
Last edited:

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
My range is the entire column. That explains why!!!
It certainly does. Hopefully you have a more user-friendly list now! :)
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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

Well-known Member
Joined
May 7, 2012
Messages
1,071
Office Version
2019
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,222
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top