Run-time error 1004 when using Auto_Open() in Excel

run time error 1004 you cannot use this command on a protected sheet
runtime error 40036
excel macro protect sheet
run macro on protected sheet
userinterfaceonly
vba protect sheet
vba to unprotect sheet

So I've created a simple macro that loops through all worksheets and AutoFits all columns. It works when I run it manually but I want it to run each time I launch Excel automatically. I placed it in a module and named the sub Auto_Open(). The problem is that I'm getting

"Run-time error '1004': Method 'Worksheets' of object '_Global' failed."

each time I start up Excel now.

Here's the code. The debugger says its the For Each portion that is causing the problem but I don't understand why. What am I missing? Thanks for any help.

Sub AUTO_OPEN()
'
' AutoFit_All_Columns Macro
'

Dim ws As Worksheet

For Each ws In Worksheets
    ws.UsedRange.Columns.AutoFit
Next ws

End Sub

For Each ws In ThisWorkbook.Worksheets

Hello, all. When I launch Excel 2000 with an Auto_Open macro in my PERSONAL.XLS workbook, I receive a "Run-time error '1004': Method 'Range' of object Sub Auto_Open() 'Select the first "signature" cell I thought Auto_Open was a legacy command and Workbook_Open is the preferred method. Can't remember using Auto_Open before. I was reading it as a Workbook_Open event and I'd still argue that ThisWorkbook would be preferred - Me limits where you can put the code, while ThisWorkbook would allow me to pull the AutoFit procedure into a standalone procedure that I can call from anywhere.

I think you need to use ActiveWorkbook. Try this code :

Sub AUTO_OPEN()
    '
    ' AutoFit_All_Columns Macro
    '

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Columns.AutoFit
    Next ws

End Sub

Error in MS Excel VBA:Run-time error '1004': Method 'Calculation' of object '​_Application' failed. In the Auto_Open() subroutine of an Excel Add  Unlike the worst tech problems out there, the cause of Run-time error 1004 within Microsoft Excel is known. Run-time error 1004 occurs when the macro you are running is copying the original worksheet to a workbook with a defined name that you did not save and close before you ran the macro.

I found an alternative using the Workbook_Open method that seems to work just fine. Placing this in the "ThisWorkbook" portion of the built-in Personal VBAProject will run AutoFit on all columns automatically when any workbook is opened.

Option Explicit
Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal WB As Workbook)

    Dim ws As Worksheet
    For Each ws In WB.Worksheets
        ws.Columns.AutoFit
    Next ws

End Sub

(auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True . Excel error 1004 is one such annoying runtime error that mainly encounters while working with the Excel file. Or while trying to generate a Macro in Excel document and as a result, you are unable to do anything in your workbook.

Excel is designed not to run Auto_Open and Workbook_Open code when a Unfortunately, this (desired) behaviour also applies when opening workbooks through VBA code. Function ShiftPressed() As Boolean This gave the run-​time error 1004 because I was referring to workbook.xls instead of workbook.xlsx. Run-time error '1004': Application or object-defined error Cause This behavior occurs when the Excel chart contains more legend entries than there is space available to display the legend entries on the Excel chart.

I'm pretty new to vba and keep getting a run time error 1004 on the highlighted line when i run the following macro <code> Sub auto_open() With that said runtime error 1004 typically occurs when using Microsoft Excel. The 1004 is a general code that relates to Microsoft Excel, but isn’t specific to one particular cause, so the exact reason why you are experiencing this error will vary, based on circumstance.

Run-Time error 1004; Method range of object worksheet failed. VBA-excel page Save As PDF Run-Time Error 1004 Mac Excel Office 2008 macro SUCCESSFULLY (for years): Sub Auto_Open() With ActiveWorkbook . Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. Additionally, some scammers may try to identify themselves as a Microsoft MVP.

Comments
  • This won't stop the error which I think has been answered already, but I'd remove UsedRange as that could return an incorrect range - just use ws.Columns.AutoFit to reference all columns on the sheet (any blank columns shouldn't be affected).
  • @BigBen The problem with Me is that it will only refer to ThisWorkbook if it's used in the ThisWorkbook module. In a worksheet it will refer to the worksheet, in a form to the form, in a normal module it returns an error, in a class it returns the class. As I read on a Chandoo page: Me refers to the parent object from which the code is "sitting" in. ThisWorkbook on the other hand always refers to the workbook that the code is in.
  • I thought Auto_Open was a legacy command and Workbook_Open is the preferred method. Can't remember using Auto_Open before. I was reading it as a Workbook_Open event and I'd still argue that ThisWorkbook would be preferred - Me limits where you can put the code, while ThisWorkbook would allow me to pull the AutoFit procedure into a standalone procedure that I can call from anywhere.
  • @DarrenBartrup-Cook I can see the merits of that.
  • I get the feeling that it might be running but it doesnt look like it's doing anything because depending on how your data looks AutoFit might not actually change the column's size
  • I believe it's because it's trying to apply the AutoFit to the Personal.XLSB workbook instead of the one that I double-clicked on. The columns of the workbook that I opened myself are definitely not resizing when using "ThisWorkbook.Worksheets".
  • I gave this a shot as well. The macro still works when I run it manually but now when I open up a file in Excel it gives me a different error. "Run-time error '91': Object variable or With block variable not set."