Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Macro-Free workbook Error

This is a discussion on Macro-Free workbook Error within the Excel Questions forums, part of the Question Forums category; Hi all, Whenever i run my subfunction I get the following error message: "The following Features cannot be saved in ...

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Posts
    160

    Default Macro-Free workbook Error

    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 by WindsorKnot; Jan 11th, 2009 at 01:07 AM.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,710

    Default Re: Macro-Free workbook Error

    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?
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  3. #3
    Board Regular
    Join Date
    Jan 2009
    Posts
    160

    Default Re: Macro-Free workbook Error

    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.







  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Macro-Free workbook Error

    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.
    JSW: Try and try again: "The way of the Coder!"

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    26,710

    Default Re: Macro-Free workbook Error

    Quote Originally Posted by Joe Was View Post
    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.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Macro-Free workbook Error

    You are correct. I dyslexia'ed it!
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,094

    Default Re: Macro-Free workbook Error

    Try using:
    Code:
    ThisWorkbook.SaveAs myPath & myfile, -4143

  8. #8
    Board Regular
    Join Date
    Jan 2009
    Posts
    160

    Default Re: Macro-Free workbook Error

    Rorya,

    Thanks this did the trick.

  9. #9
    New Member
    Join Date
    Feb 2011
    Posts
    20

    Default Re: Macro-Free workbook Error

    Quote Originally Posted by Joe Was View Post
    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.

  10. #10
    Board Regular
    Join Date
    Nov 2009
    Location
    North Shore
    Posts
    150

    Default Re: Macro-Free workbook Error

    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
    Excel 2010

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com