Application / Object Error 1004

[Garry]

Hello

I have the above error msg on this line:-

Private Sub cmdAddBtn1_Click()
ActiveWorkbook.Sheets("HEA Payments").Activate

What is wrong or how do i reference it?

Tx.


garrygdc

 

[Tom Ogilvy]

Your code ran fine for me.

Code was in the worksheet module of a sheet having cmdAddBtn1 and this
sheet was not HEA Payments.
HEA Payments and the sheet with the code were in the same workbook.

 

[Garry]

Tom

Is was working fine for me then started with that error??

The actual worksheet is on a network drive that everyone has access
too?? full path is F:\hea..\...\payments.xls.

Having said that no-one should be accessing it as they would not know it
was actually there.

Are there any flags or settings that I should be resetting?


garrygdc

 

[Tom Ogilvy]

If it is in another workbook, then the code should be

Private Sub cmdAddBtn1_Click()
ActiveWorkbook.Sheets("HEA Payments").Activate
Activeworkbook.Sheets("HEA Payments").Range("A1") _
.End(xlDown).Offset(1, 0).Select

End Sub

 

[Garry]

Tom

Tx for the quick reply.

No that didn't work either. I tried a reboot too.

Could I be Activating the workbook/sheet too many times?

What would be the fully qualified statement?

Tx
garrygdc

 

[Tom Ogilvy]

Well there is no way to click a button on a worksheet and have another
workbook be the activeworkbook - should have recognized that fallacy from
the start. If you want to select a range in another workbook, you would
need to activate that workbook first.

Private Sub cmdAddBtn1_Click()
Workbooks("HEA Payments.xls").Activate
ActiveWorkbook.Sheets("HEA Payments").Activate
Activeworkbook.Sheets("HEA Payments").Range("A1") _
.End(xlDown).Offset(1, 0).Select

End Sub

Change the first line of code to reflect the actual name of the workbook.

 

[Garry]

Tom

Tried that but still the same.

I tried moving it all to the initialise stetement when the form loads
but no effect.
So done this on a new workbook

Private Sub CommandButton1_Click()
Workbooks("test.xls").Activate
ActiveWorkbook.Sheets("Sheet1").Activate
ActiveWorkbook.Sheets("Sheet1").Range("A1") _
.End(xlDown).Offset(1, 0).Select

MsgBox "done that"
End Sub

Same error at the same point??

Is it me?? I have winxp pro and excel 2000
garrygdc

 

[Tom Ogilvy]

I created a userform in a new workbook (not test.xls), put one commandbutton
on it and pasted in your code.

I then had workbook Test.xls open, but not active. I showed the userform
and clicked the commandbutton and it ran fine for me. (as expected).

Win 2000 and Excel 2002 but I have done similar in xl97, xl2000, Win 98SE

The only thing I can think of that would cause this error is if there is no
Data below A1. If you do End(xldown) and the column is empty you end up at
A65536, then trying to offset 1 cell below that (which doesn't exist) would
cause a 1004 error. If that is not the case then:

You can try application.Goto

Private Sub CommandButton1_Click()
Application.Goto workbooks("Test.xls"). _
Worksheets("Sheet1").Range("A1").End(xldown).Offset(1,0)
End Sub

Or you can just reference that cell and not try to select it
Dim rng as Range
set rng = workbooks("Test.xls"). _
Worksheets("Sheet1").Range("A1").End(xldown).Offset(1,0)
rng.Value = 21