Error 1004 - Saving Macros in Macro-Free Workbooks

[rjbinney]

I have a big honking spreadsheet, with lots of INDEX and LOOKUP formulas and one big macro.

I need to access it on my mobile - which doesn't tolerate the macros or formulas.

Many years ago - with the help of folks on this board - I created that macro, which:

• Saves a copy of the Master
• Re-sorts the data
• Strips the formulas
  

  Cells.Select    Range("b1").Activate
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Application.CutCopyMode = False

• Removes the Macro's Button
  

  ActiveSheet.Buttons.Delete

• Then saves as a "usable" worksheet... And that's where trouble has started

It used to be that I just saved it as an XLS file (e.g., "ActiveWorkbook.SaveAs Filename:="Inventory for Dropbox.xlsx", FileFormat:=51) and it would save no problem.

Now I am getting a run-time 1004 error, and the debugger highlights that line and says "VB projects and XLM Sheets cannot be saved in a macro-free workbook."

As far as I can tell, I've made no changes to the structure of the file or the macro. (I've compared to archived copies). I presume(d) that by saving it as XLSX it just disabled or stripped the macros.

My guess is I need to add a line in the macro that removes macros? That feels like it creates some kind of Morton-salt-girl recursiveness (i.e., how can you have a macro remove macros from a file that's running the macro that tells it to remove a macro...).

My macro settings are at "Disable VBA macros with notification" - and I believe they have been for a long time. In an attempt to "fix" this challenge, I ticked the "Trust access to the VBA project object model" box.

I am open and happy to any elegant solution!

Incidentally, the last time this macro ran correctly was about six weeks ago - the same day I added XL2BB! I have since removed it, on the remote chance there's a connection, but the issue persists).

 

[MARK858]

Do you have a Application.DisplayAlerts = False line in your code?

 

[Fluff]

This is a know bug that recently appeared. VB projects and XLM sheets cannot be saved in a macro-free workbook

 

[MARK858]

This is a know bug that recently appeared. VB projects and XLM sheets cannot be saved in a macro-free workbook

It's why I asked if the OP has the line (the OP hasn't shown it in the code code snippets in the question)

 

[rjbinney]

Do you have a Application.DisplayAlerts = False line in your code?

I do! It's the first line in the code. (Sorry - I didn't think you'd need every line in there... I tried to anticipate what questions would be asked...!)

I put that line in there because I am doing a few Save As routines in the macro, and that prevents it from asking me to confirm.

And, yes, I am on the Beta channel.

Thanks for the link, Fluff. I'll look on Windows Insider for where to *****.

 

[MARK858]

I do! It's the first line in the code. (Sorry - I didn't think you'd need every line in there... I tried to anticipate what questions would be asked...!)

Not a problem, it is just this one was an issue we are aware of and so we knew what we were looking for.
Sorry that we can't offer a solution, rather than just an explanation.

Btw, I think it applies to most versions (there are some odd exceptions as you'll see in the other thread Fluff still has the old dialog box, even though his version looks the same as the version Dominic updated to) and not just the Beta channel, as it looks like they changed the dialog in an update without sending it to the Insiders for feedback first

 

[rjbinney]

It would appear this issue has been resolved... Running V2402 17304.20000 CtR (Beta Channel)