Macro-Free workbook Error

WindsorKnot

Board Regular
Joined
Jan 4, 2009
Messages
160
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
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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?
 
Upvote 0
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.


Macroerror-1.jpg




johnnyc1019
 
Upvote 0
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.
 
Upvote 0
Try using:
Code:
ThisWorkbook.SaveAs myPath & myfile, -4143
 
Upvote 0
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.
:unsure: Beginning with the paragraph, "I set security...", I did not understand what you wrote.
 
Upvote 0
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 :confused:

And the code I had on the worksheet is gone too
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top