Converting normal cell into DropDown List

[jaybul]

Hi, I am trying to conver a normal excel cell into a dropdown list with defined values at runtime. Request help.

[Bob Phillips]

Just go to Data>Validation, choose a type of list, and enter the range reference in the box.

[jaybul]

Just go to Data>Validation, choose a type of list, and enter the range reference in the box.

Thanks for your post. Actually, I am trying to do this at runtime from a VBA code. Sorry that I did not explicitly mention about it in my first post. Any help is appreciated.

[Bob Phillips]

Just do it from in Excel with the macro recorder switched on, you will get all the code that you need.

[lucas]

[VBA]Option Explicit
Sub NumberRowsAndCreateList()
Application.ScreenUpdating = False
[A:A].ClearContents
With [A1:A10000]
.Formula = "=ROW(B1)"
.Value = .Value
End With
With [D110].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$A:$A"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "ERROR"
.InputMessage = "Select from list"
.ErrorMessage = "Please select from dropdown list only"
.ShowInput = True
.ShowError = True
End With
ActiveWorkbook.Save
[D1].Select
Application.ScreenUpdating = True
End Sub
[/VBA]

[Bob Phillips]

I'll bet the macro recorder never came up with this abomination

[vba]

[A:A].ClearContents
[/vba]

[lucas]

Your right Bob....no need for that but it couldn't be much worse with the recorder.

[jaybul]

Just do it from in Excel with the macro recorder switched on, you will get all the code that you need.

Thanks a looooot! Why did it not click to me? Thanks again.