Run-time error 1004 when using Auto_Open() in Excel
runtime error 40036
excel macro protect sheet
run macro on protected sheet
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.
- This won't stop the error which I think has been answered already, but I'd remove
UsedRangeas that could return an incorrect range - just use
ws.Columns.AutoFitto reference all columns on the sheet (any blank columns shouldn't be affected).
- @BigBen The problem with
Meis that it will only refer to
ThisWorkbookif it's used in the
ThisWorkbookmodule. 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.
ThisWorkbookon the other hand always refers to the workbook that the code is in.
- I thought
Auto_Openwas a legacy command and
Workbook_Openis the preferred method. Can't remember using
Auto_Openbefore. I was reading it as a
Workbook_Openevent and I'd still argue that
ThisWorkbookwould be preferred -
Melimits where you can put the code, while
ThisWorkbookwould 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
AutoFitmight 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."