Formulas Defined Names - Forgot to save macro enabled

[ronanbaker1]

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]

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!)