1004 Select Method error

Question

I need to cycle through the first 3 sheets of an Excel workbook and execute some code when it gets opened.

This is my code:

    Private Sub Workbook_Open()

Dim I As Integer

For I = 1 To 3
  Sheets(I).Select
  sam_crm = Range("I2").Value
  ActiveSheet.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=sam_crm
  ActiveSheet.ListObjects(2).Range.AutoFilter Field:=5, Criteria1:=sam_crm
  ActiveSheet.ListObjects(2).Range.AutoFilter Field:=1, Criteria1:="<>*" & sam_crm & "*", Operator:=xlAnd
Next I   
Sheets(1).Select 
End Sub

I get

error 1004, select method of the worksheet object could not be executed

I'm using the German version of excel, so I don't know the exact English error message. It is working fine with Excel 2007, but since the last update it isn't working on the newer versions.

Substitute `.Select` for `.Activate`, I would make an answer, but thing is too short to be one. — AntiDrondert, Feb 16 '18 at 12:11
I also tried .Activate. The error message changes to "error 1004, activate method of the worksheet object could not be executed" — Dirk Sachse, Feb 16 '18 at 12:13
What would the purpose of this code be? Why not just activate `sheet(1)`. — Davesexcel, Feb 16 '18 at 12:14
@Davesexcel because I m doing 3 times the same thing. I m setting filters to the string in cell "I2" of every sheet. — Dirk Sachse, Feb 16 '18 at 12:19
Avoid `.Select` and `.Activate` at all that's a bad practice use `Sheets(I).ListObjects(1)…` directly. Also specify a sheet for your ranges `Sheets(I).Range("I2").Value` — Pᴇʜ, Feb 16 '18 at 12:27
@Pᴇʜ perfect, that works! Now I just need to make sure that the first sheet is active, once it is done with the loop. — Dirk Sachse, Feb 16 '18 at 12:35
In this case you would need a `.Select` or `.Activate` like `Sheets(1).Select` if it does not work that might be because the workbook is still not fully loaded when `Workbook_Open` runs. You can try to add a `Do: DoEvents: Loop While Not Application.Ready` before `Sheets(1).Select` then. — Pᴇʜ, Feb 16 '18 at 12:41

score 0 · Answer 1 · answered Feb 16 '18 at 12:54

0

Use Option Explicit to force proper variable declare
Try to avoid .Select and .Activate and ActiveSheet. instead specify a worksheet by its name.

Here is an improvement of your code

Option Explicit 'forces variable declare 

Private Sub Workbook_Open()
    Dim i As Long 'use long instead of integer, there is no advantage in integer
    For i = 1 To 3
        Dim sam_crm As Variant
        With Sheets(i) 
            sam_crm = .Range("I2").Value 'due to the WITH statement above this is the same
                                         'like sam_crm = Sheets(i).Range("I2").Value
                                         'but just shorter
                                         'and the same also for .ListObjects below …
            .ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=sam_crm
            .ListObjects(2).Range.AutoFilter Field:=5, Criteria1:=sam_crm
            .ListObjects(2).Range.AutoFilter Field:=1, Criteria1:="<>*" & sam_crm & "*", Operator:=xlAnd
        End With
    Next i   

    'it might be that the workbook is not fully loaded at this point so
    'ThisWorkbook.Sheets(1).Select might fail. So we can try to wait until the
    'Application is ready:
    Do: DoEvents: Loop While Not Application.Ready 'runs a loop until Application.Ready = True

    ThisWorkbook.Sheets(1).Select 'or .Activate is OK here because we really want to 
                                  'select it so this is what the user sees.
End Sub

answered Feb 16 '18 at 12:54

Pᴇʜ

56,719
10
49
73

`use long instead of integer, there is no advantage in integer` reduced memory space? – AntiDrondert Feb 16 '18 at 13:26
@Pᴇʜ thanks a lot! The last part is still not working, but I noticed this only occurs when I open the file directly from Outlook. If I save it on a disk, it works just fine. – Dirk Sachse Feb 16 '18 at 13:36
@AntiDrondert reduced memory space is true for a 16 bit system only, on a 32 bit system, a 16 bit integer gets silently converted to a long without the benefit of the larger range of numbers to work with. [Here](https://stackoverflow.com/a/26409520/3219613) are some further infos and link about that fact if you are interested. So this is why we can safely say always to use `Long` in VBA (except when talking to an old 16 bit API or something that forces you to use `Integer`). There is no benefit in `Integer` at all. – Pᴇʜ Feb 16 '18 at 14:07
@DirkSachse I know that there are some odd effects when using files directly from Outlook. Maybe you could ask exactly that as a question here on Stack Overflow, I'm out of ideas why this happens in combination with Outlook. My last idea: You could try the `Workbook_Activate()` event instead of the `Open` event to select the sheet. – Pᴇʜ Feb 16 '18 at 14:11
@DirkSachse If this solved your issue consider marking this answer as solution. – Pᴇʜ Feb 16 '18 at 15:11

1004 Select Method error

1 Answers1