Run-Time Error 1004 on Excel Startup

[J. Henke]

Hello, all.

When I launch Excel 2000 with an Autpen 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:
"Range("AE1").Select").

If I am to run the macro manually, it executes properly. However,
this Autpen 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 Autpen()

'Select the first "signature" cell
Range("AE1").Select

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

'Select column A
Columns("A:A").Select

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

'Select the first row in the spreadsheet
Rows("1:1").Select

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

'Select cell A1
Range("A1").Select

'Declare a variable to store the number of rows in the active
worksheet
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
string
myCellVal = "A1:A" & numRowsInSheet

'Insert 1 in cell A1 for the first RecordID
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select

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

'Initialize a general property in columns 1 through 42 to
ensure that 42 columns get
'some sort of tab delimiter
Range("A1:AP1").Select
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
Else
MsgBox "Sorry chum, this isn't an acceptable spreadsheet!",
vbInformation, "Sorry!"
End If
'
End Sub

 

[Rob Bovey]

The code in your Autpen 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
Autpen 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
http://www.appspro.com/

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

 

[J. Henke]

Interesting...

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

Thanks!

J. Henke

 

[Rob Bovey]

What you're doing is not possible to do in the Autpen 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
''' Autpen procedure here.
MsgBox Wb.Name & " was just opened."
End If
End Sub

------------
In Module1
------------
Public gclsEventHandler As Class1

Sub Autpen()
Set gclsEventHandler = New Class1
End Sub

Both of these modules go in Personal.xls. Once Personal.xls has opened
and run its Autpen 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
http://www.appspro.com/

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

 

[J. Henke]

Rob,

Your example worked like a charm!

Thank you so much for all of your help.

J. Henke