How can I create a drop-down box programmatically in Excel?

Question

I have a spread sheet which has a drop down in sheet1. Can anyone please tell me how to make a drop down in sheet3 of that same spread sheet assuming that it has three sheets named sheet1, sheet2, and sheet3. The program is shown below.

  Range("A1").Select

  With Selection.Validation

    .Delete

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

    xlBetween, Formula1:="=$B$1:$B$3"/*Guess i need to make some change in this*/

    .IgnoreBlank = True

    .InCellDropdown = True

    .InputTitle = ""

    .ErrorTitle = ""


    .InputMessage = ""

    .ErrorMessage = ""

    .ShowInput = True

    .ShowError = True

  End With

Thanks a lot

Answer 1

When you record a macro while using basic Excel functions to make a dropdown (Data > Validate, then select Allow: list), you can look at the generated code and edit it to your liking. You can re-use the code afterwards.

Answer 2

Define a dynamic named range with the anchor as the insert point for your returned recordset.

Then, in the cell you wish to have the drop-down, type =(name of named range) - the drop-down options will then be whatever is returned from your database query.

Example:

Define a named range as "Options1" with formula =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B$1:$B$20),1)

Set your database query to return the results to cell B1

Select cell A1 and open the Validation dialog (Data>Validation)

Select List and then enter =Options1 as the Source

The above can be done programmatically by using

ActiveWorkbook.Names.Add Name:="Options1", RefersTo:="=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B$1:$B$20),1)"

With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Options1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Answer 3

Create a "named range" out of $B$1:$B$3 then use the named range in the formula. If you do not want to use a named range then specify the sheet name in the formula, as in =Sheet1!$B$1:$B$3.

For help on naming cells in workbooks, see the "Name cells in a workbook" help topic and check out the "Name a cell or a range of cells" section.

• Named Ranges in Microsoft Excel at http://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
• Excel Named Range at http://spreadsheets.about.com/od/exceltips/qt/named_range.htm

Answer 4

If the data that you are using to populate the dropdown exists on each sheet, then you can select each sheet and add the validation. See the code below.

Sub Dropdowns1()

With Worksheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$B$1:$B$3"
End With

With Worksheets("Sheet3").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$B$1:$B$3"
End With

End Sub

If the data that you are using to populate the dropdown only exists on sheet1, then you need to first create a named range that refers to the data and then use the named range to pupulate the dropdown.

The code below creates a named range MyList that refers to the range B1:B3 on Sheet1. It then uses the named range on Sheet1 and Sheet3 to poulate a dropdown list.

Sub Dropdowns2()

ActiveWorkbook.Names.Add Name:="MyList", RefersTo:="=Sheet1!$B$1:$B$3"

With Worksheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=MyList"
End With

With Worksheets("Sheet3").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=MyList"
End With

End Sub