Drop down List

[garydp]

Is it possible to create a Drop down List at runtime. i have a list of names that i want to be able to select from a specific cell when my worksheet is created. i cant use a combobox as i cant asign a macro to it.

[Bob Phillips]

You can assign a macro to a forms combobox, and control toolbox comboboxes have click events.

[garydp]

the problem i have is because the form is created at runtime with the combobox i want to be able to select a name from the list and enter it into a specific cell. because the page isnt created until the command button is pressed i dont know were to put the code as each sheet has a different name. so i could have ten sheets with the same format and a combobox created at runtime for each with the same data in. but at present i dont know how to store the code for the combobox as i am unable to assign the macro like i can with a command button

thats why i though maybe a drop down box would be better

[Bob Phillips]

What sort of combobox are you creating, forms or toolbox. Either way, you should be able to link a cell so that you can read that to get the selected item (index at least).

[garydp]

thanks i have sorted it out now, i have creted a drop down box in runtime in the cell were i was adding the combobox originaly.

i used this code

Dim MyList, MyFirst As String

MyList = "Apple,Orange,Pear,Banana"
Range(r2).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=MyList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(r2).Value = MyFirst

thanks for your help

[Bob Phillips]

Good choice, a much more stable solution IMO.

[kirbz]

Hi garydp,

May I know where did you put the code?

Bec I also want to have a dropdown list from an specific cell and not on a form..

is that what you have done?

Thanks..

[kirbz]

Hi garydp and xld,

Please ignore my previous message as I already figured out how to do it, I have put it on a sub routine..

Anyways, I have another question..

because what I wanted to do is create a drop down list but the value of the drop down list will depend on the value of another cell..

for example if the value of W1 = "PC" the value of the cell with the drop down list will contain "CTN and BOX" which is Y1.. but if the value of W1 = "BDL" the value of Y1 will contain "ROLL and GNY"...

and in addition.. the change of contents of Y1 will occur when I have selected Cell Y1..

is it possible and how to code it in VBA?

Thanks. hop you understand what I mean...

[Bob Phillips]

If you have a question, start your own thread, don't hijack another.

[kirbz]

owh.. sorry.. did not know.. I thought.. i can also ask a question here.. anyways.. i'll juz start my own thread..