Sheet names to Cell values

[KentKHI]

Is there a way to get a range of cells to show all of my current sheet names in that workbook? I need it to update any time I add, delete, or change a sheet name.

Thanks

 

[Smitty]

Check out Zack's Table of Contents.

As for your update criteria, that might require just a wee bit more effort.

Hope that helps,

Smitty

 

[KentKHI]

WOW That is cool!

Now, to get it to update, There is no such thing as a workbook_change event, is there?

Any other thoughts to start off with?

Thanks a bunch for the link

 

[Smitty]

There is a Workbook_SheetChange event:

SheetChange Event
This example runs when any worksheet is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
End Sub

You could test for the ActiveSheet.Name and if it doesn't match what's in the list, then rerun the TOC code.

Smitty

 

[KentKHI]

Hey Smitty!

Good to hear from you again! Thanks for the sub name!

I got it to work on any change, but what do you think I should do if I want it to only look at worksheet names?

Right now, the code runs any time I make a change in a worksheet, but I can delete a worksheet, and it won't change.

I'm not sure how to trigger this

 

[KentKHI]

Got it.

I just set up my code to run everytime my "create or delete new workbook code" runs

So never mind my gibberish, and thanks again for a great lead Smitty.