Error code 1004 VBA when sent via email

[m8ttwindsor]

Hi all, apologies if this is in the wrong thread.

I'm getting a runtime error code 1004 "Activate method of worksheet class failed" when I send my worksheet via an email. The code only happens when you open the file as an attachment but not when i open the original file from my desktop.

The error derives from

Private Sub Workbook_Open()
Worksheets("sheet1").Activate
frmstudentregister.Show
End Sub

Any help is much appreciated

[jmcilhinney]

Yeah, it's in the wrong forum. The VB.NET forum is for VB.NET questions. If your question is on VBA, as you say, then you should put it in the VBA forum. I've asked the mods to move this thread to the correct forum.

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[TnTinMN]

These types of glitches can be hard to track down and it may be that it is bug in Excel VBA. Therefore I'm going to throw a two different suggestions to try. No guaranties that either will work. Note that I'm using the "ThisWorkBook" qualifier; it shouldn't be needed, but who knows.

1st: Make sure that the workbook is the active one.

Private Sub Workbook_Open()
   ThisWorkbook.Activate
   ThisWorkbook.Worksheets("Sheet1").Activate
End Sub

2nd: It may be a timing issue due to opening it from an attachment. Let's try running that code with a delay.

Private Sub Workbook_Open()
   ' schedule to run in 5 seconds
   Call Application.OnTime(DateAdd("s", 5, now()), "ThisWorkbook.ActiveSheet1")
End Sub

Private Sub ActiveSheet1()
   ThisWorkbook.Activate
   ThisWorkbook.Worksheets("Sheet1").Activate
End Sub

Good luck.