Create dependable dropdown of unique items in vba

0 votes

Using VBA, I want to produce a trustworthy drop-down with distinct items for Store and Region from the table in the below image in cells D4 and F4, respectively. The user initially chooses selections from the shop drop-down in cell D4 before selecting the options for the trustworthy region in cell F4.

I can design distinctive drop-down menus for shops and areas, but they are unreliable. Also, the manner I make the customised drop-down menu is ineffective. I record a macro while following the steps in this article.

enter image description here

Apr 3, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

Try this:

Sub UniqueList()

    Dim rng As Range, rng2 As Range
    Dim cell As Range, cell2 As Range
    Dim dict As Object, dict2 As Object
    Dim i As Long, j As Long

    Set dict = CreateObject("Scripting.Dictionary")
    Set dict2 = CreateObject("Scripting.Dictionary")

    Set rng = ActiveSheet.Range("A2", ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp))
    Set rng2 = ActiveSheet.Range("B2", ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp))

    For Each cell In rng
        If Not dict.exists(cell.Value) Then
            dict.Add cell.Value, i
            i = i + 1
        End If
    Next cell

    For Each cell2 In rng2
        If Not dict2.exists(cell2.Value) Then
            dict2.Add cell2.Value, j
            j = j + 1
        End If
    Next cell2

    With ActiveSheet.Range("D4").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(dict.keys, ",")
    End With

    With ActiveSheet.Range("F4").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(dict2.keys, ",")
    End With

End Sub
answered Apr 3, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

VBA how to calculate depth of items in excel, simliar to a BOM system

Add on the sheet an ActiveX Microsoft ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Creating an unique export data VBA Macro Function in Excel

Unfortunately, the tables will vary in size. ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Highlighting Unique List of Words in Each Cell of a Selection of Cells - Excel VBA

In a Textbox it is a vbcrlf ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

How to create Dropdown list in excel using php

Try this: $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP