Excel - List Sheet Names Coming up as Blocked

Re: "I want to make a list of sheet names in an Excel workbook"

Here is a vba code option for you...

'---[Edited]

Sub ListSheetNames()
Dim Ndx As Long, sh As Object

Ndx = 2
With ThisWorkbook.Worksheets(1)
.Cells(Ndx, 1).Resize(ThisWorkbook.Sheets.Count * 2, 1).ClearContents
For Each sh In ThisWorkbook.Sheets
.Cells(Ndx, 1).Value = sh.Name
Ndx = Ndx + 2
Next 'sh
End With
End Sub

'---

Nothing Left to Lose

Hi I got the same question. It worked fine in a particular workbook yesterday, but it isn't now. I have tried re-defining & re-inputting the formulas, still get #BLOCKED.

However, it works in a new workbook. So it's kind of weird which I have no idea on how to solve the #BLOCKED in my original workbook and afraid the same will happen again even if I switch all the data to that new workbook.

Hi I got the same question. It worked fine in a particular workbook yesterday, but it isn't now. I have tried re-defining & re-inputting the formulas, still get #BLOCKED.

However, it works in a new workbook. So it's kind of weird which I have no idea on how to solve the #BLOCKED in my original workbook and afraid the same will happen again even if I switch all the data to that new workbook.

RE: "still get blocked"

As EliseM_456 referenced, xl4 Macros have been disabled in recent versions of Excel.

The Get.Workbook function is an xl4 function and is listed in the Microsoft Excel

Function Reference book, copyright 1992 (536 pages)
Even if the function works occasionally, you should find a replacement.

MS continually finds ways to obsolete earlier versions of its programs and force

users into the monthly rental program for the current versions.

'---

Nothing Left to Lose

Thanks for your reply!

My office should be a 2016 one with windows 10, so that means it may just like what you said.

However, I'm a non-macro/VBA user and dont quite sure how I should incorporate the vba code you mentioned at the beginning. May I know do you have any other non-vba recommendation as a replacement of this xl4 function (which I was just copying and pasting formulas to make it works)?

I got loads of tabs with and would like to get all those tab names at once and make them with hyperlinked.

Thank you!

Re: "I got loads of tabs with and would like to get all those tab names at once and make them with hyperlinked."..."do you have any other non-vba recommendation as a replacement of this xl4 function"

Yes, I do. The free "Add Table of Contents" workbook will create a TOC for any (single) open workbook.

Each name on the TOC is hyperlinked to the named worksheet.

The list is generated in the visible range on the new sheet (the list goes down and then across). I have a ~180 sheet workbook that uses it. Easy to use, just

a couple of clicks...

Note: if you want to create a deeper list, hide the formula bar or the Ribbon before

creating the TOC list.

Download from OneDrive...

Re: Add Table of Contents workbook

Would like to hear if you tried the workbook and if so, were there

any problems with it.

Nothing Left to Lose

Sorry, I have been away for a bit. Yes!! the Table of Contents does work and hiding the Ribbon did let the list go farther down. I only have 22 sheets right now and it was starting a new column around sheet 18 I think.

As I add sheets to the workbook, can I refresh the TOC within the workbook? Or do I call up the TOC workbook and run it again?

Thank you for sharing this with us. It is quite useful and you are very kind to do so.

Thanks for your reply. I've been away for a bit. Today when I opened the workbook, it came up with a warning about Trusted Macros or something (sorry I didn't take a screenshot and now it's gone). I clicked on that and enabled xl4 Macros and everything seems to work again - even after closing and reopening the workbook. Yea!

Also the Table of Contents that Nothing Left to Lose shared works well too.

Thanks all for your help!