Make a dynamical dropdown box in Excel

[Hermanator]

Hello all,

I want to do something, but I'm not sure if it's at all possible.

In Excel, I want to have a "pick from list" in a certain cell. The options in that list, should vary, depending on the number and names of the worksheets in the excel file.
I have at all no idea wether VBA can be used to assign some kind of complex function to a certain cell, or wether you can only use it to make procedures that run at a certain time.
So what I want is a pick-list in (for instance) cell A1, which I can use to pick the name of all the worksheets in the file. If the file has 3 worksheets (Sheet1, Sheet2 & Sheet3), the pick list should have those 3 options (Sheet1, Sheet2, Sheet3). If I would add a sheet, and give it a certain name, it should appear in the pick list automatically. (Without manually having to run a macro).

Is something like that at all possible?

Many thanks!

 

[xlbo]

have a look at Data>Validation for the drop down list

I think you will be out of luck for getting a list of sheet names without code though. If it only needed to be generated once, it would be simple enough but for it to change based on inserting / deleting sheets would require VBA I would think

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

you can use a macro and put it in the Workbook_NewSheet event.

This way the macro fires every time you add a sheet and you don't have to run it manually.

Cheers,

Roel

 

[Hermanator]

Great!

I didn't realise that it's possible to trigger a VBA macro in excel. I have made a macro and it works OK. There's only one problem. Although the macro gets triggered correctly when I manually insert a worksheet, it doesn't trigger when I copy a worksheet from another workbook into the file!

I haven't found a trigger which works also when a sheet is copied into the workbook.

 

[Rofeu]

I wouldn't know an event that handles that specific occasion.

I think that the best way for you would be to have your macro fire whenever the cell with the list is activated.
(Worksheet_Selectionchange when target = listbox) This would cover sheet-renaming as well.

Cheers,

Roel