none
VBA error 1004 in Excel 2007

    Question

  • I have a VBA program that has worked in the past, but suddenly is giving me an error indicating that workbook in which I am attempting to insert a template-based worksheet in is protected. The code snippet that fails is

    Sheets.Add Type:=strDailySheetTemplateLocation

    The error is "Workbook is protected and cannot be changed". The workbook is not protected, the sheet I am inserting is not protected. Any idea what might be going on here?

    Thanks

    Sunday, June 10, 2012 8:35 PM

Answers

All replies

  • Hi Geb,

    The parameter for type is suspicious.  According to this MSDN help, the value is enum XLSheetType but doesn't accept customized value:

    Name

    Value

    Description

    xlChart

    -4109

    Chart

    xlDialogSheet

    -4116

    Dialog sheet

    xlExcel4IntlMacroSheet

    4

    Excel version 4 international macro sheet

    xlExcel4MacroSheet

    3

    Excel version 4 macro sheet

    xlWorksheet

    -4167

    Worksheet

    On the other hand, VBA runtime error 1004 indicates a name failure: http://support.microsoft.com/kb/146864

    Another reference for this error: http://stackoverflow.com/questions/1250484/excel-2007-vba-run-time-error-1004

    Does this help fix it?


    Forrest Guo | MSDN Community Support | Feedback to manager

    • Marked as answer by Geb Blum Tuesday, June 12, 2012 7:22 AM
    • Unmarked as answer by Geb Blum Tuesday, June 12, 2012 7:23 AM
    • Marked as answer by Geb Blum Tuesday, June 12, 2012 7:23 AM
    Tuesday, June 12, 2012 2:48 AM
  • Thanks, Forrest. I found a two year old copy of the template and it works. I cannot find any difference between the two templates. This is program was a quick and dirty Excel application I wrote to save one of our secretaries a lot of mindless work, and unfortunately has become institutionalized here and has grown like topsy. My next problem will be dealing with 64 bit Excel 2010, as Microsoft has dropped the calendar control, and that is a very key piece of the application. Thanks for your time.
    Tuesday, June 12, 2012 7:29 AM
  • Glad to know that you resolved it.  Feel free to create new threads for new problem.

    thanks,


    Forrest Guo | MSDN Community Support | Feedback to manager

    Tuesday, June 12, 2012 8:51 AM