Macro-Free workbook Error

WindsorKnot · Jan 11, 2009

Hi all,

Whenever i run my subfunction I get the following error message:

"The following Features cannot be saved in a macro-free workbooks:

VB Project

To save a file with these features, click no, and then choose a macro-enabled file type...."

As expected, when one clicks no it results in a runtime 1004 error. Is it possible not to have this warning message shown everytime? My concern is some of my coworkers who are not vba friendly may get confused and click no, causing the code to crash.

This is my current code which runs fine, minus the macro-free error

Code:

Sub pastespecial()


Dim lsheets As Worksheet

For Each lsheets In Worksheets

lsheets.Activate
Cells.Select
Selection.Copy
Selection.pastespecial Paste:=xlPasteValues, Paste:=xlFormats

Next lsheets

Application.CutCopyMode = False

MyDate = Format(Date, "mm-dd-yyyy")
myfile = ThisWorkbook.Name & MyDate & "email" & ".xls"
mypath = ThisWorkbook.Path & "\"

ThisWorkbook.SaveAs myfile & Path



 MsgBox "New Range-Valued Workbook has been created"


End Sub

Peter_SSs · Jan 11, 2009

WindsorKnot

I haven't actually tested your code, but I can make a few comments about it.

1. It is not a good idea to name a procedure using one of vba's 'special' words. So 'pastespecial' is not a good name for a procedure.

2. You have 'Dim'd lsheets, but not myDate, myfile or mypath. I think it is a good idea to Dim all variables. You can force yourself to do that by putting Option Explicit right at the top of your Module. And you can make that happen automatically by, in the VB window...
Tools|Options...|Editor tab|tick 'Require variable declaration'|OK

3. ThisWorkbook.SaveAs myfile & Path
This line appears to have two errors:
a) You have used 'path' when I think you meant 'mypath'
b) The path should come before the file name

So this should have abetter chance of success:
ThisWorkbook.SaveAs mypath&myfile

BTW, what version of Excel are you using?

WindsorKnot · Jan 11, 2009

Thanks Peter.

Point taken on the Dims and procedure names. I gotta work on figuring out how to define it for mypath

That said I followed your note on this step

ThisWorkbook.SaveAs mypath&myfile

and I'm still getting the VBA Alert message. If I click yes the code works, if no it fails.

Also, I'm using Excel 2007 for my home pc and 2003 for the work which makes things interesting on occasion.

Joe Was · Jan 11, 2009

That is part of security!
You need to go into each system that will be ruining your application and change the macro security and go into trusted sources and check the box for access to the VB Project.

In 2007 this is available from the upper Right Option Icon, the one that displays the print and save options!
At its bottom, once displayed, go to the box button for Excel Options.

I set security to low, it does nothing anyway but block code to set it higher. Some people who download a lot of workbooks may want to be warned if a workbook contains code before opening, but as all the workbooks I use do have code this is useless for me, so I use the low security setting. The low setting opens workbooks with code without any warnings. Also, set the show developer tab and trust addins, for full access.

The Office help has a good step by step on how to do these security settings.

Peter_SSs · Jan 11, 2009

Joe Was said:
You need to go into each system that will be ruining your application ...

Joe, I love that typo!

In 2007 this is available from the upper Right Option Icon, ...

Not so sure about this one though - it's on the left in my version.

Joe Was · Jan 11, 2009

You are correct. I dyslexia'ed it!

RoryA · Jan 11, 2009

Try using:

Code:

ThisWorkbook.SaveAs myPath & myfile, -4143

WindsorKnot · Jan 11, 2009

Rorya,

Thanks this did the trick.

kstvns · Nov 2, 2011

Joe Was said:
That is part of security!
You need to go into each system that will be ruining your application and change the macro security and go into trusted sources and check the box for access to the VB Project.

In 2007 this is available from the upper Right Option Icon, the one that displays the print and save options!
At its bottom, once displayed, go to the box button for Excel Options.

I set security to low, it does nothing anyway but block code to set it higher. Some people who download a lot of workbooks may want to be warned if a workbook contains code before opening, but as all the workbooks I use do have code this is useless for me, so I use the low security setting. The low setting opens workbooks with code without any warnings. Also, set the show developer tab and trust addins, for full access.

The Office help has a good step by step on how to do these security settings.

Beginning with the paragraph, "I set security...", I did not understand what you wrote.

z168 · Nov 17, 2011

I have this problem too. Theres only 2 users of the file and both have been set to "enable". Code used to work fine but now it wont let me and I get the error

And the code I had on the worksheet is gone too

Macro-Free workbook Error

WindsorKnot

Board Regular

Peter_SSs

MrExcel MVP, Moderator

WindsorKnot

Board Regular

Joe Was

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Joe Was

MrExcel MVP

RoryA

MrExcel MVP, Moderator

WindsorKnot

Board Regular

kstvns

New Member

z168

Board Regular

Similar threads

Share this page

Macro-Free workbook Error

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

Board Regular

New Member

Board Regular

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock