Share via

VBA error occurs on only 1 machine

Anonymous
2015-05-06T12:30:10+00:00

I have created a macro that runs fine on my Windows 8.1 PC with Excel 2013. My client has Excel 2013 on a Windows 7 PC. She is receiving a Run-Time error 1004 Select method of Worksheet class failed.

I have checked both the Excel Add-Ins and References in Excel and VBA and have even had her re-install Office 365 without success.

The macro runs on every other computer that we've tried it on.

The line of code causing the problem is: Worksheets("Summary").select  Originally the line was ws.main.activate but then that line caused the break so I commented that line out and added the select line. There is definitely a worksheet named Summary. The Sheet is protected at this point but the workbook is not.

The code is below and I've underlined and italicized the relevant line.  The code is in the ThisWorkbook section as is triggered by the Workbook_Open event.

I would greatly appreciated any help in resolving this issue.

Private Sub Workbook_Open()

    Dim wsMain As Worksheet, wsFr As Worksheet

    Dim YN As Integer, Reg As Boolean, X As Integer

    Set wsMain = Worksheets("Summary")

    If IsEmpty(wsMain.Range("b4")) = True Then

        YN = MsgBox("It looks like you are ready to setup a Market Survey file" & Chr(10) & _

            "for a new property.  Would you like to create a sheet for your" & Chr(10) & _

            "subject property now?", vbInformation + vbYesNo, "New Survey File")

        If YN <> vbYes Then Exit Sub

        If YN = vbYes Then

            OrderWorksheets

            AddNewComp True

            wsMain.Unprotect PW

            With wsMain.Range("b4")

                .Interior.ColorIndex = 10

                .Font.ColorIndex = 2

            End With

            wsMain.Protect Password:=PW, Contents:=True, Scenarios:=True

        End If

    End If

    Set wsFr = Worksheets("Formulas")

    Reg = False

    For X = 1 To wsFr.Range("n1").End(xlDown).Row

        If Application.UserName = wsFr.Range("n" & X).Value Then

            Reg = True

            Exit For

        End If

    Next

'    wsMain.Activate

Worksheets("Summary").Select

    wsMain.Unprotect PW

    Select Case Reg

        Case Is = True

            ActiveSheet.Shapes("Button 2").Select

            Selection.Characters.Text = "Submit to Corporate"

            Selection.OnAction = "SubmitToCorporate"

        Case Is = False

            ActiveSheet.Shapes("Button 2").Select

            Selection.Characters.Text = "Submit to Regional"

            Selection.OnAction = "SubmitToRegional"

    End Select

    wsMain.Protect PW

    Range("b5").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2015-05-06T17:56:38+00:00

Minor change within Excel Trust Center/Protected Mode fixed the final problem

All is good.  Moving the code from the ThisWorkbook to a regular module is what solved the original problem.

Thanks.  This problem has been solved.

Was this answer helpful?

0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-05-06T15:50:58+00:00

    I have tried all three of these solutions with no success. They continue to generate the 1004 error code.

    However, the last suggestion generated the error on the Applicaiton.OnTime Now line, 1004 - Method 'OnTime' of object '_Application' failed.

    Another weird thing is that I had her open two older files (basically the same thing). One worked and the other did not. I ran a VBA code compare on all of the modules and they are identical.

    Any more ideas?

    BTW - Thanks so much for trying.....

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-05-06T14:25:25+00:00

    Another thing to consider is moving out the code that currently is in Workbook_Open and putting it a sub called something like "ContinueOpen" in a normal module. Replace what's in WOrkbook_Open with:

    Application.Ontime Now, "'" & ThisWorkbook.FullName & "'!ContinueOpen"

    Sometimes, the workbook_Open event is triggered before Excel is done doing its opening housekeeping work. This trick ensures you give Excel time to handle everything prior to running your code.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-05-06T13:14:24+00:00

    Hi

    Is the correct workbook containing that sheet acthe active one ?

    Like

    Set  wsmain = workbook("myworkbook").Worksheets ("Summary")

    It is ususally safer to use the sheets code name

    Next

    Next, one you make a reference to an object use it !

    '    wsMain.Activate

    '   Worksheets("Summary").Select

    wsMain.Unprotect PW  'this one is OK by itself

    With wsmain.Shapes("Button 2")

        Select Case Reg

            Case Is = True

                .Characters.Text = "Submit to Corporate"

                .OnAction = "SubmitToCorporate"

            Case Is = False

                .Characters.Text = "Submit to Regional"

                .OnAction = "SubmitToRegional"

        End Select

    End with

        wsMain.Protect PW

    Was this answer helpful?

    0 comments
  4. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2015-05-06T12:53:59+00:00

    Try replacing the last part (from 'wsMain.Activate') with this:

        With wsMain

            .Unprotect PW

            If Reg Then

                With .Buttons("Button 2")

                    .Caption = "Submit to Corporate"

                    .OnAction = "SubmitToCorporate"

                End With

            Else

                With .Buttons("Button 2")

                    .Caption = "Submit to Regional"

                    .OnAction = "SubmitToRegional"

                End With

            End If

            .Protect PW

        End With

    Was this answer helpful?

    0 comments