Run-time error 57121 on specific machines only

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
Have you tried setting the value to string instead of a variant?:

Code:
[COLOR=#333333]Function SheetExists(sSheet [/COLOR][COLOR=#ff0000][B]as String[/B][/COLOR][COLOR=#333333]) As Boolean[/COLOR]
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 :eek:
 
Last edited:

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
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.
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
I'd make sure each variable is explicitly declared. have you got a
Code:
Dim sMailTo as String
line?
 

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
I replaced

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

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

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
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. >.<
 

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
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
 

jpjosey

New Member
Joined
Mar 7, 2017
Messages
6
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,147
Messages
5,442,650
Members
405,191
Latest member
wedloski

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top