# Formulas Defined Names - Forgot to save macro enabled



## ronanbaker1 (Sep 23, 2022)

Hi,
I created a defined name as follows. Formula - Name Manager - New - Name: sheetname Refers to: =GET.WORKBOOK(1)&T(NOW())

I then made a formula to put each sheet name in the subsequent row. =IFERROR(INDEX(MID(sheetname,FIND("]",sheetname)+1,255),ROWS($A$1:A1)),"")

It worked at the beginning. What I failed to do is save as a macro enabled file. Once I closed out of the file, and tried to reopen, the values were blank (As the second part of my formula triggers). If I remove the IFERROR piece, the value would state #BLOCKED! If helpful, the formula changed to include *{*=IFERROR(INDEX(MID(sheetname,FIND("]",sheetname)+1,255),ROWS($A$8:A16)),"")*}*

After some research I found out it was because I didn't save it as a macro enabled file. I then tried to re-save as but the formula will not populate a value.

Can anyone help me? I tried to delete the defined term, clear out my formula, Save the file and close... Re-open, do the steps above again but this time save as a macro enabled file but each time I close and re-open the formula changes to blank.


----------



## BaGRoS (Dec 16, 2022)

Maybe:









						Enabling Excel 4.0 macros and their use in a LAMBDA function
					

I recently found the 2 year old video Excel Formula to List All Sheet Tab Names and include Hyperlinks which included a sample file. The solution created a Named Range called SheetNames that used the formula  =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())  However, both the...




					www.mrexcel.com
				






> Short version of the fix, go to *File -> Options -> Trust Center -> [Trust Center Settings] -> Macro Settings -> *check the* Enable Excel 4.0 macros when VBA macros are enabled *box, recommended settings under *Macro Settings* is* Disable VBA macros with notification*, then select *File Block Settings* on the left, and make sure* Excel 4 MacroSheets and Add-in Files* is checked in the Open column, and under *Open behavior for selected file types*, select *Open selected file types in Protected View and allow editing*. (not short, but as short as possible!)


----------

