Removing Particular value from a Drop Drown form control using VBA

[LalitPandey87]

Hi,

I need a code which removes a particular value from the Drop Down (Form Control) using VBA.
Actually the condition is when the Drop Drown fill first time there is a value which is "Select Value" in the first
position
Whenever a user select any value from the Drop Down then this value should be Removed so the user can not select this
value again.
And after removing this value the index should be Reindex.

It will a great help for me

Thanx in Advance.
:o

[Excel Fox]

Worksheets("Sheet1").DropDowns("NameOfDropDown").RemoveItem 1

[LalitPandey87]

Thanx for giving me ur time from ur busy schedule.

I applied this but its not working for me. I don't know what i am doing wrong.

[Excel Fox]

Have you changed the sheet name and dropdown name as required?

[LalitPandey87]

Yaa,

but it gives some error. and this command which you wrote is for Form Control(does'nt mean that i am creating user forms i placed the control in a sheet)
I really need it

Thanx.

[Excel Fox]

I have used the same code in the attachment

[LalitPandey87]

Its Gives me

Run-Time Error '1004'
RemoveItem method of DropDown class failed

Error when i am writing the same in my excel file.

I am filling this List Box by a namedrange.:confused:

[Excel Fox]

You cannot have the listfill range and expect to remove an item from the list!! Instead of having the listfill through a range, why don't you add each item. Then you will also be able to remove item

[LalitPandey87]

Yup Now its working.....

Thanx