PDA

View Full Version : Converting normal cell into DropDown List



jaybul
01-15-2007, 03:47 PM
Hi, I am trying to conver a normal excel cell into a dropdown list with defined values at runtime. Request help.

Bob Phillips
01-15-2007, 04:24 PM
Just go to Data>Validation, choose a type of list, and enter the range reference in the box.

jaybul
01-16-2007, 03:30 AM
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
01-16-2007, 05:05 AM
Just do it from in Excel with the macro recorder switched on, you will get all the code that you need.

lucas
01-16-2007, 10:09 AM
Option Explicit
Sub NumberRowsAndCreateList()
Application.ScreenUpdating = False
[A:A].ClearContents
With [A1:A10000]
.Formula = "=ROW(B1)"
.Value = .Value
End With
With [D1:D10].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

Bob Phillips
01-16-2007, 10:16 AM
I'll bet the macro recorder never came up with this abomination :p



[A:A].ClearContents

lucas
01-16-2007, 10:23 AM
Your right Bob....no need for that but it couldn't be much worse with the recorder.

jaybul
01-16-2007, 10:31 AM
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.