Run-Time Error 1004 on Excel Startup


J. Henke

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
'_Global' failed."

It's particularly confusing because when I debug, I do not see any
problems with the code (it breaks on a line with the code:

If I am to run the macro manually, it executes properly. However,
this Auto_Open procedure is giving me (and apparently Excel) fits.

Any suggestions would be wonderful.

Thank you,

J. Henke

P.S. Below is the macro's code:

Sub Auto_Open()

'Select the first "signature" cell

'Verify whether or not this is a spreadsheet to convert
If ActiveCell.Value = "Commission %" Then

'Select column A

'Insert a new column, and shift the other columns to the right
Selection.Insert Shift:=xlToRight

'Select the first row in the spreadsheet

'Delete the header row and shift up
Selection.Delete Shift:=xlUp

'Select cell A1

'Declare a variable to store the number of rows in the active
Dim numRowsInSheet As Integer

'Count the number of rows in the active worksheet and store
them in a variable
numRowsInSheet = ActiveSheet.UsedRange.Rows.Count

'Declare a new string
Dim myCellVal As String

'Store the new range (with the variable number of rows) in the
myCellVal = "A1:A" & numRowsInSheet

'Insert 1 in cell A1 for the first RecordID
ActiveCell.FormulaR1C1 = "1"

'Use Excel's AutoFill function to fill the whole range of
cells with 1's for the RecordID
Selection.AutoFill Destination:=Range(myCellVal),

'Initialize a general property in columns 1 through 42 to
ensure that 42 columns get
'some sort of tab delimiter
Selection.Font.Italic = True
Selection.Font.Italic = False

'Change to the directory in which to save the project
ChDir "C:\My Documents"

ActiveWorkbook.SaveAs Filename:="C:\My
Documents\myFlatFile.txt" _
, FileFormat:=xlText, CreateBackup:=False
MsgBox "Sorry chum, this isn't an acceptable spreadsheet!",
vbInformation, "Sorry!"
End If
End Sub



Rob Bovey

The code in your Auto_Open procedure assumes that there will be an
ActiveWorkbook to operate on. Personal.xls is a hidden workbook which opens
prior to any other workbooks being created. Therefore, when the Personal.xls
Auto_Open procedure runs, there are no other workbooks open and any attempt
to reference range objects will fail.

Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

J. Henke


That is what I thought maybe was happening, but do you
know if there is anyway to activate the workbook I am
trying to open (e.g. by double-clicking on a .xls file)
before running the code?

Or is what I am attempting to do simply not plausible with


J. Henke

Rob Bovey

What you're doing is not possible to do in the Auto_Open procedure of
Personal.xls. If you're trying to watch for workbooks the user opens in
Excel and take action when they do, this is possible using a more advanced
technique that involves setting up a WithEvents class module in
Personal.xls. This class will be instantiated when Personal.xls loads and
will notify you any time a workbook is opened via an Application-level
WorkbookOpen event. Here's a basic example.

In Class1
Private WithEvents mxlApp As Excel.Application

Private Sub Class_Initialize()
Set mxlApp = Excel.Application
End Sub

Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub

Private Sub mxlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
''' Ignore this workbook and any add-ins.
If Wb.Name <> ThisWorkbook.Name And Not Wb.IsAddin Then
''' Put the code that used to be in your
''' Auto_Open procedure here.
MsgBox Wb.Name & " was just opened."
End If
End Sub

In Module1
Public gclsEventHandler As Class1

Sub Auto_Open()
Set gclsEventHandler = New Class1
End Sub

Both of these modules go in Personal.xls. Once Personal.xls has opened
and run its Auto_Open procedure, the mxlApp_WorkbookOpen event procedure in
Class1 will fire any time the user opens a workbook, including if they have
started Excel by double-clicking on a workbook from Explorer.

Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *



J. Henke


Your example worked like a charm!

Thank you so much for all of your help.

J. Henke

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel run-time error '1004' 0
Run-Time Error 1004 0
Run Time Error '1004': 2
Run time 1004 Error 0
Run-time error '1004' 5
Run-time error '1004' 2
Run-time Error '1004' 0
run-time error 1004 exiting Excel 2007 2