A few words of thanks would be greatly appreciated.

Excel - Create drop down list on certain cells

Latest update on October 3, 2012 at 04:28 AM by deri58 .

Issue
Solution

Issue

I wanted to create a drop down list from data in Sheet2. I have tried the following codes but the outcome seems to be incorrect. Instead of the data in Sheet2 listed in the drop down list, the drop down list contained the data from Sheet1 (which is my destination sheet).

Also, I would like to have the drop down list on certain cells where I have information on the cells before, i.e. the drop down list should be in Column F of Sheet1 where there is information on Column E of Sheet1 and repeat until the end of the list.

Herewith attached is the file I am working on here.

Solution

You create a validation list from ANOTHER WORKSHEET give a range name to that validation list(in sheet2)
and use that as the formula

I tried to modify your macro without disturbing except changing one or two lines
I have given range name as "rangename". you can change it in the macro

If necessary tweak it little more.

Sub Dropdown()
Dim x As Long, y As Long
Dim objCell As Range
Dim objDataRangeStart As Range
Dim objDataRangeEnd As Range
Dim rangename As String
' >> Set The Range For Valid Data
Set wsSourceList = Sheets("Sheet2")
Set wsDestList = Sheets("Sheet1")
Set objDataRangeStart = wsSourceList.Cells(1, 2) 'Start range for dropdown list entries
Set objDataRangeEnd = wsSourceList.Cells(6, 2) 'End range for dropdown list entries
MsgBox objDataRangeStart
MsgBox objDataRangeEnd
'=================
With Worksheets("Sheet2")
Range(objDataRangeStart, objdatarangaeend).Name = "rangename"
End With
'=====================
' >> Set Validation On Required Cell
'Set objCell = wsDestList.Cells(8, 4) 'Location of the dropdown list
'MsgBox objCell
x = 4
y = 6

Do
Set objCell = wsDestList.Cells(x, y) 'Location of the dropdown list

With objCell.Validation
  .Delete
  '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & objDataRangeStart.Address & ":" & objDataRangeEnd.Address
  '===========================================
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=rangename"
  '================================================================
  .IgnoreBlank = True
  .InCellDropdown = True
  .ErrorTitle = "Warning"
  .ErrorMessage = "Please select a value from the list available in the selected cell."
  .ShowError = True
End With
x = x + 1
'y = y + 1
Loop Until x = 51

End Sub

Thanks to venkat1926 for this tip.

Excel vba drop down list in cell
Excel vba create drop down list in cell - Best answers
Vba create drop down list in cell - Best answers
Excel 2007 drop down list not working ✓ - Forum - Excel
Excel - Cascading drop-down list - How-To - Excel
Excel vba find last non empty cell in column - How-To - Excel
Excel create drop down list with multiple columns - How-To - Excel
Excel vba open file path in cell - How-To - Excel

This document, titled « Excel - Create drop down list on certain cells », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Excel - Using the CONCATENATE function

Excel - Automate the data entry based on specific date

Issue

Solution

Subscribe To Our Newsletter!