I want to make a list of sheet names in an Excel workbook. I used the following to set this up: Go to the Formulas tab. Press the Define Name button. Enter SheetNames into the name field. Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") Hit the OK button. Enter Index in A1 and SheetNames in B1 Starting in A2 enter 1,2,3,... for all sheets in workbook. In B2 enter =INDEX(SheetNames,A2). Copy and paste this down the column. This worked once. I saved the workbook as a macro-enabled workbook but now I just get #BLOCKED! as a result instead of the sheet name. I did rearrange the sheets but even re-creating the above still gives me #BLOCKED!. I'm working with Office365 on a PC with Windows 10. A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. Anonymous 2022-07-23T19:24:16+00:00 I want to make a list of sheet names in an Excel workbook. I used the following to set this up: Go to the Formulas tab. Press the Define Name button. Enter SheetNames into the name field. Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") Hit the OK button. Enter Index in A1 and SheetNames in B1 Starting in A2 enter 1,2,3,... for all sheets in workbook. In B2 enter =INDEX(SheetNames,A2). Copy and paste this down the column. This worked once. I saved the workbook as a macro-enabled workbook but now I just get #BLOCKED! as a result instead of the sheet name. I did rearrange the sheets but even re-creating the above still gives me #BLOCKED!. I'm working with Office365 on a PC with Windows 10. Go to File > Options > Trust Centre > Trust Centre Settings > Macro Settings > Check the option 'Enable Excel 4.0 Macros when VBA macros are enabled' 0 comments No comments I want to make a list of sheet names in an Excel workbook. I used the following to set this up: Go to the Formulas tab. Press the Define Name button. Enter SheetNames into the name field. Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") Hit the OK button. Enter Index in A1 and SheetNames in B1 Starting in A2 enter 1,2,3,... for all sheets in workbook. In B2 enter =INDEX(SheetNames,A2). Copy and paste this down the column. This worked once. I saved the workbook as a macro-enabled workbook but now I just get #BLOCKED! as a result instead of the sheet name. I did rearrange the sheets but even re-creating the above still gives me #BLOCKED!.

I'm working with Office365 on a PC with Windows 10. Go to File > Options > Trust Centre > Trust Centre Settings > Macro Settings > Check the option 'Enable Excel 4.0 Macros when VBA macros are enabled' Anonymous 2022-05-27T02:59:51+00:00 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 0 comments No comments 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 Anonymous 2022-05-27T06:52:47+00:00 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! 0 comments No comments 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. Anonymous 2022-05-27T02:18:07+00:00 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. 0 comments No comments 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. Anonymous 2022-05-22T20:24:31+00:00 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 https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (try the free 'Add TableofContents' workbook ) 0 comments No comments Re: "I want to make a list of sheet names in an Excel workbook" Here is a vba code option for you... 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 https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (try the free 'Add TableofContents' workbook )