Excel Function Macro: Creating a drop down list

excel vba drop down list macro
excel vba drop down list change event
excel drop-down list
vba drop down list userform
excel formula based on drop down list
excel macro pop up box with drop-down menu
dynamic drop down list excel vba
combo box excel

I want to create a function that turns the cell into a dropdown list with values in it.

So far from what I read online, it only shows how to do it in sub form but I want to try to use it in the cell so it automatically populates it. So far I got this.

function test(val as Range)
    if val = 1 then
        set test = Range(name_of_defined_list)
    elseif val = 2 then
        test = "no list"
    end if
end function

And in the cell it would be written and dragged down to the cells below


Answer your first row question: I want to create a function that turns the cell into a dropdown list with values in it.

if you want this one then you can try this code:

Sub test()
'drop down list
Dim MyList(5) As String ' create array with definite size and value
MyList(0) = 1 ' assign value
MyList(1) = 2
MyList(2) = 3
MyList(3) = 4
MyList(4) = 5
MyList(5) = 6

'put in C2 array MyList
    With Range("C2").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(MyList, ",")
    End With
    End Sub

or you can use un array with size dinamically and get items from cells

Sub test()
'drop down list example with 5 items into list
Dim MyList() As String
Dim i, myDim As Integer

myDim = 5 ' number of items that you can get from a cell
ReDim MyList(myDim)

'get items from cells. example A1-B1-C1-D1-E1
For i = 0 To myDim
    MyList(i) = Cells(1, i + 1) ' assign value into MyList
Next i

'cell A10 is mi drop down list
With Range("A10").Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With
End Sub

Hope this help you

Excel Function Macro: Creating a drop down list, To run macros by clicking command button is a common task in Excel, but, Firstly, create a drop down list as you need, see screenshot: formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more​. To run macros by clicking command button is a common task in Excel, but, have you ever tried to execute the macro codes based on the value which is selected from drop down list? It means, when you choose one item from the drop down list, the specific macro will be triggered at once.

There is a way to work around this type of requirement, but it might not meet your needs...

Step 1. Create a function to be called when a user clicks on the in-cell validation list drop down. This example checks the value to the left of the cell and only if it's "Yes" does it return a range to be used for the list.

Function Lister()
    Dim rv As Range
    If TypeName(Application.Caller) = "Range" Then
        If Application.Caller.Offset(0, -1).Value = "Yes" Then
            Set rv = Sheet1.Range("A1:A5")
        End If
    End If
    Set Lister = rv
End Function

Step 2. You can't reference this function directly from the validation list setup, so you need to use a workbook name to "route" the validation call to the function:

Step 3. Create your in-cell drop-down, referencing the name you just created (which in turn links to the VBA function). Now you'll get a list when the cell to the left has "Yes", but no list otherwise. Or you could instead use the same approach to return two different lists, depending on the content in other cells.

How to run macro based on value selected from drop down list in , A combo box is a drop-down list from where a user can select an item or fill in his/​her own choice. To create a combo box in Excel VBA, execute the following  Go to the Data tab on the Ribbon, then click Data Validation. On the Settings tab, in the Allow box, click List. If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell.

Okay, so after researching from all the answers I was provided, I found this link, https://www.excel-easy.com/vba/events.html

I was able to write a Change event Sub so that when the user adds a value to a cell and switches out of the cell, the sub runs and creates the dropdown list.

Excel VBA Combo Box, On creating a drop down list on a cell or cell-range, one drop-down will be displayed Now we will see how to make Drop Down List in Excel 2003, 2007 and 2010. Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now! Excel Formula, Excel Functions, Popular Articles|Tags: drop down, list, named  Notes: You can copy & paste the drop-down list cell (2nd step) anywhere in your sheet.But, if you delete the data from selected cells(1st step), you won’t see data in drop down list. For example, if you delete item1, you will see only item2,item3 and item4 in the drop down list. VBA macro to create drop down list

How to Create Drop Down List in Excel, The rest of the lines in the function Update_DataValidation creates a drop down list in the cell “J2” in sheet2. You can download the file and code  Usually I open a new sheet, name it something no one would ever consider using (like “far43fq”) and print the data there. Step 2: Select the cell you would like to add the drop down list to. Then click on the Data Validation button on the Data Ribbon: Step 3: Select list: Step 4: Input the range of the data.

Run a macro from a drop down list [VBA], In this section, you will learn the exacts steps to create an Excel drop-down list: Using Data from Cells. Entering Data Manually. Using the OFFSET formula. #1  The following example shows you how to use Excel formulas to create a variable drop-down list. (If you are not familiar with drop-down lists in Excel, you may want to first view the page on How to Create a Basic Drop-Down List in Excel). How to Create a Variable Drop-Down List - Example. The spreadsheet below contains a list of members of staff

Data Validation 6, In this tutorial, we are going to look at how to create a Data Validation Drop-Down List in a cell using VBA. Creating a Drop Down List Using VBA Note: An  Here are the steps to create an Excel Drop Down List: Select a cell where you want to create the drop down list. Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

  • You shouldn't use a function for this. Use a Sub Procedure (there is a reason you didn't find a way to do it with a function in your research)
  • You're comparing a range to an integer on your code. Besides, I think you can't turn a cell to a droplist with a function. You have to do it with a macro.
  • to elaborate on what @Pspl said, if val is a range larger than 1 cell, this will produce an error. If val is just one cell and contains an error (from formula) or is non-numeric (text) this will also produce an error
  • @urdearboy Ah, I need to do it in a Sub Procedure and then add it to my function. That makes sense. I don't know why I didn't think of that. So I store the Procedure in my Function and I just use ActiveCell and it will know the cell it is currently in?
  • Would I be able to use this Sub and add it into my Function to use in every cell?
  • yes try the first...copy and paste in your vba module and keep me update...i tried and works. Try only my code to understand if works well for you. After we try to put in oyur function...
  • I added the Procedure into my function so I could add it like this in the cell: =testFunc() and in the Procedure I did ActiveCell.Validation..... but it doesn't work. Does the Procedure need to be on it's own?
  • Sorry, try in this way first. Use only my code and use f8 to execute the code step by step and after click on C2 cell(if your are using the first block of code).
  • Yeah, the Procedure works fine by itself when executed but I was wondering more on it executing automatically when someone fills in a value in a cell