Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Run-time error 57121 on specific machines only

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Run-time error 57121 on specific machines only

    I've had some issues lately where upon emailing a file it loses some functionality, but only for me...

    Prime example:

    I have a file that was working for me, on 64b 2013, I sent it to other people with 32b 2010 and 32b 2013, and it works fine for them.

    However it has at some point stopped working for me on both my 64b 2013 and 32b 2010 machines. Despite the exact same file still working for other people.

    An example of the error is...

    Code:
    Function SheetExists(sSheet) As Boolean
    
    
    SheetExists = False
    For Each ws In ActiveWorkbook.Worksheets
        If sSheet = ws.Name Then
            SheetExists = True
            Exit Function
        End If
    Next ws
    
    
    End Function
    I can see in the locals window at the error point that sSheet = "Report" and ws.Name = "AdminOnly" -- however it errors on the line;

    Code:
     If sSheet = ws.Name Then
    With the error message;
    Code:
    Run-time error '57121':  Application-defined or object-defined error.
    I have enabled both macros and activex controls completely. I tried a the office repair tool. I rolled back to a system restore from when it was working. I reinstalled office. I did a fresh install of windows with a clean office install. Problem persists.

    At the rate I'm pulling my hair out I'm going to be bald by the end of the week.

    What am I missing here? Why can my colleagues run the macros but I cannot?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,353
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    If you have ActiveX controls on your worksheets, I suspect they are the cause: Office Update breaks ActiveX controls | Excel Matters
    Note that there have been several occasions where this kind of thing has happened with an Office update. If at all possible, I'd suggest you switch to Form controls as they are much more stable and not prone to this issue.

  3. #3
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,517
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    Have you tried setting the value to string instead of a variant?:

    Code:
    Function SheetExists(sSheet as String) As Boolean
    Edit: Ahh just saw the first reply. Didn't think of those. I use Active X with no issues (thankfully) though after reading that I may have to change them for future compatibility
    Last edited by gallen; Mar 7th, 2017 at 05:33 AM.

  4. #4
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    Interesting.

    I added

    Code:
    dim ws as Worksheet
    and it doesn't bug there, but does bug out with the same error here;
    Code:
     sMailTo = Worksheets("AdminOnly").Cells(2, 5).Value
    Despite having just pulled various other values from OTHER sheets in exactly the same way. There's something about this AdminOnly sheet that's screwing things up and I can't put my finger on it.

  5. #5
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,517
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    I'd make sure each variable is explicitly declared. have you got a
    Code:
    Dim sMailTo as String
    line?

  6. #6
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    I replaced

    Code:
    sMailTo = Worksheets("AdminOnly").Cells(2, 5).Value
    with

    HTML Code:
    Dim ws_admin As WorksheetSet ws_admin = Worksheets("AdminOnly")sMailTo = ws_admin.Cells(2, 5).Value
    and it works fine now! What on earth?!

  • #7
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    Quote Originally Posted by gallen View Post
    I'd make sure each variable is explicitly declared. have you got a
    Code:
    Dim sMailTo as String
    line?
    Yeah that didn't work...

    But the above did.

    Why all of a sudden does it require explicits only for a specific tab on specific machines? I don't mind being explicit, but functionality shouldn't just stop working for no apparent reason. >.<

  • #8
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    Quote Originally Posted by jpjosey View Post
    I replaced

    Code:
    sMailTo = Worksheets("AdminOnly").Cells(2, 5).Value
    with

    Code:
    Dim ws_admin As WorksheetSet ws_admin = Worksheets("AdminOnly")sMailTo = ws_admin.Cells(2, 5).Value
    and it works fine now! What on earth?!
    Sorry, fixed the code tag -- couldn't find the edit button

  • #9
    New Member
    Join Date
    Mar 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    Quote Originally Posted by jpjosey View Post
    I replaced

    Code:
    sMailTo = Worksheets("AdminOnly").Cells(2, 5).Value
    with

    Code:
    Dim ws_admin As WorksheetSet
     ws_admin = Worksheets("AdminOnly")
    sMailTo = ws_admin.Cells(2, 5).Value
    and it works fine now! What on earth?!
    ugh

  • #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,353
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run-time error 57121 on specific machines only

    With the ActiveX problems I mentioned, I've seen compile errors on a module that had nothing other than Option Explicit in it. As soon as the ActiveX controls were removed, the errors went away.

  • User Tag List

    Like this thread? Share it with others

    Like this thread? Share it with others

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    DMCA.com