I want to create a list of all thetabs dynamically.

lets say i have three Sheets A ,B and Search.

Then i want the names A and B to appear in the Search Tab in the cell B2 dropdown..

If i add any new tab in between A and b or after B or delete any tab then i want it to immediately reflect in the tab Search tab...

Please explain the coding and also try it in Excel as far as possible and not in VB..
IDeally it should look like this.

In search Tab cell B2.

" "
A
B

the first option by default in the Drop-down should be blank and the other should be the names of the tabs..

Hi there,

I think you'll need to use VBA code - that's the only way you can keep track of adding/deleting worksheets.

Do you want me to propose a VBA solution?

Best regards,

Greg M

Originally Posted by Greg M

Hi there,

I think you'll need to use VBA code - that's the only way you can keep track of adding/deleting worksheets.

Do you want me to propose a VBA solution?

Best regards,

Greg M

Dear Greg, thanx for offerieng to help.
I dont know Vb therefore i was requesting for Excel Coding anyways i would appreciate if u could do it in VB as well and consider all the things mentioned like adding/deleting tabs and also one more thing i would like to emphasize is that i can have the drop-down in B2 but the Vb coding should provide me a chance to choose the location of the Drop-Down as in a real-Environment i can place it anywhere so please dont hard-code it only to B2 and also is it possible to have the default option of first blank in the Drop-down and please please please provide a detailed explanation for your coding.


Just one more thing how can i learn more about Visual basic coding application with Excel or without Excel..

Do v have forum for the same?

Thanx so much once again.

Hi All4Excel,

I hope the attached workbook helps to get things started.

The workbook contains a sheet called "Search" which is the heart of the action. This sheet contains a named range called "SheetNames" which holds the names of each of the sheets in the workbook & is used to populate the dropdown list for the combobox. This range is updated automatically (using VBA code) whenever a worksheet is added or deleted.

The combobox can be moved to wherever you wish on the "Search" sheet.

The first row of the "SheetNames" range (and therefore the dropdown list) will always contain a blank as you requested.

At present the dropdown list does NOT include the "Search" worksheet itself - I can include this very easily if you wish.


The workbook contains two VBA code modules, named "ThisWorkbook" and "Module1". This code might not be the easiest for a beginner to understand, but I'll try to give you the general method of operation.

The main code is the "UpdateList" routine. This first clears the existing list of worksheet names and deletes the existing "SheetNames"named range. The code then takes the names of each of the worksheets (apart from the "Search" worksheet) & enters them in column A of the "Search" worksheet, beginning at cell A2. Having done this, the code updates the "SheetNames" range to reflect the number of worksheets the workbook now contains.

Now all we need to do is to trigger the "UpdateList" routine. Whenever a new worksheet is added, that sheet is Activated (selected). Similarly, whenever a worksheet is deleted, some other worksheet in the workbook is Activated. Excel itself calls a routine called "Workbook_SheetActivate" whenever a worksheet is Activated, so we can use that routine to call our "UpdateList" routine.

To avoid calling the "UpdateList" routine when we're simply navigating around the worksheets we use a simple test in the "Workbook_SheetActivate" routine. The variable "intTotalSheets" contains the number of sheets in the workbook & is updated (by our code) when the workbook is opened and also by the "UpdateList" routine. Whenever the "Workbook_SheetActivate" routine is called we check the number of sheets the workbook contains & if that is NOT equal to the value contained in "intTotalSheets" it means that a worksheet has been added or deleted & so we call the "UpdateList" routine in such a case.

I hope this gives you some understanding of what's going on, but please feel free to ask me for further information.

Best regards & happy coding,

Greg M

Originally Posted by Greg M

Hi All4Excel,

I hope the attached workbook helps to get things started.

The workbook contains a sheet called "Search" which is the heart of the action. This sheet contains a named range called "SheetNames" which holds the names of each of the sheets in the workbook & is used to populate the dropdown list for the combobox. This range is updated automatically (using VBA code) whenever a worksheet is added or deleted.

The combobox can be moved to wherever you wish on the "Search" sheet.

The first row of the "SheetNames" range (and therefore the dropdown list) will always contain a blank as you requested.

At present the dropdown list does NOT include the "Search" worksheet itself - I can include this very easily if you wish.


The workbook contains two VBA code modules, named "ThisWorkbook" and "Module1". This code might not be the easiest for a beginner to understand, but I'll try to give you the general method of operation.

The main code is the "UpdateList" routine. This first clears the existing list of worksheet names and deletes the existing "SheetNames"named range. The code then takes the names of each of the worksheets (apart from the "Search" worksheet) & enters them in column A of the "Search" worksheet, beginning at cell A2. Having done this, the code updates the "SheetNames" range to reflect the number of worksheets the workbook now contains.

Now all we need to do is to trigger the "UpdateList" routine. Whenever a new worksheet is added, that sheet is Activated (selected). Similarly, whenever a worksheet is deleted, some other worksheet in the workbook is Activated. Excel itself calls a routine called "Workbook_SheetActivate" whenever a worksheet is Activated, so we can use that routine to call our "UpdateList" routine.

To avoid calling the "UpdateList" routine when we're simply navigating around the worksheets we use a simple test in the "Workbook_SheetActivate" routine. The variable "intTotalSheets" contains the number of sheets in the workbook & is updated (by our code) when the workbook is opened and also by the "UpdateList" routine. Whenever the "Workbook_SheetActivate" routine is called we check the number of sheets the workbook contains & if that is NOT equal to the value contained in "intTotalSheets" it means that a worksheet has been added or deleted & so we call the "UpdateList" routine in such a case.

I hope this gives you some understanding of what's going on, but please feel free to ask me for further information.

Best regards & happy coding,

Greg M

Hey greg thanx for your help for both my questions , however i want to have the list updated immediately after Renaming , Moving , Addding and Deleting Sheets..
You have certainly done a good job by providing the first blank and also the lenght of the Drop-Down is only till the no of tabs but i dont want to drag the formula again and again..

You are really great in developing this code but is it possible without Vb only in Excel?

In the other question falling in a range . want a simple code in excel if possible the reason being im going to use today in the cell B2 and the dates in the Data tab may be old dates having an old year but i want only those dates falling in the range from 10 days behind and 30 days ahead of the current date.. so not considering the year at all. [ DD-MMM ].

Hi again,

Ok on the list of sheet tabs. Unfortunately I think this can only be done using VBA code, as there are no Excel formulas which list worksheets.

If you want the dropdown list to be updated when you move & rename worksheets, then you'll have to artificially trigger the "UpdateList" routine (after you've done your moving/renaming) by inserting a new worksheet & then deleting it immediately afterwards. This will have the effect of updating the dropdown list to show the new sequence of worksheets. Another possibility is to place an "Update List" button (using the "Forms" toolbar) on the Search sheet & to use this to call the "UpdateList" macro after you've moved/renamed worksheets.

Anyway, I hope I've been of some assistance to you.

All the best,

Greg M

Greg you were really of help to me if its not possible in Excel its not i just need to acept it.

Its just that im used to Exel and have never worked in VB , is there any way i can learn VB from the NEt.

Just for your information you can also go throuh the seperate question on Dates falling in a certain range where Daddylonglegs answered..

I relally liked it maybe you can just go thru that asu wud also like it.

Thanx a lot brother.