Announcement

Collapse
No announcement yet.

Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA



    Hi all,

    I have a workbook that, in its original state, has all sheets hidden except for one. This original sheet has a number of buttons, and depending on the button chosen, unhides a few other sheets. All of the macros work flawlessly when run in 2003. However, as you try and run it in 2007, the following error is experienced:

    "Microsoft Visual Basic
    Run Time error 57121
    Application-defined or object-defined error"

    and the code breaks. When I try and debug this, the line highlighted is simply setting the "Visible" property of the original sheet (the one visible upon opening) to true. I have tried commenting this line out, as it seems slightly unnecessary since the sheet is always visible, but the same issue is then experienced with another line. I would include a sample workbook, but I am trying to help a colleague and my machine is running 2003, so I couldn't be sure if the sample book forces the same error. Is anyone aware of any workarounds for this issue? I have searched the internet and the forums and haven't found anything that relates closely enough to this error. Please let me know.

    Thanks in advance,
    szczesnm

  • #2
    Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

    Still stuck on this. Has anyone come across anything like this? Please help!

    Comment


    • #3
      Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

      Anyone? All of the issues all over the internet regarding 2003 to 2007 excel switches don't seem to relate to mine and I'm stumped...

      Comment


      • #4
        Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

        Originally posted by szczesnm View Post
        Still stuck on this. Has anyone come across anything like this? Please help!
        no reply yet?
        I am also looking for this exact thing. This guy is describing something spot on to the problem I am having. I am sad to see no soultion has yet been contributed.

        Comment


        • #5
          Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

          Post your code so someone can take look at it.


          @superj707,

          Feel free to stayed tuned to this thread if you think it will help you find a solution for your needs.
          However, if you have questions, start a new thread and include a link back to this one if you think it helps calrify your situation.

          Posting your question in threads started by others is known as thread hijacking and is not permitted.
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment


          • #6
            Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

            Originally posted by AAE View Post
            Post your code so someone can take look at it.


            @superj707,

            Feel free to stayed tuned to this thread if you think it will help you find a solution for your needs.
            However, if you have questions, start a new thread and include a link back to this one if you think it helps calrify your situation.

            Posting your question in threads started by others is known as thread hijacking and is not permitted.

            nope no new questions. This guy pretty much summed up my question. I agree I would not want to hijack this topic. However, I feel my needs are precise to the OP's question. IF we land on an answer here it would also be a good resource to others as well I am sure. Here is my code.

            Code:
            Sub Macro6()
            '
            ' Macro6 Macro
            ' Macro first started 02/06/2012 by jjoh302
            '
            'Application.ScreenUpdating = False
             If CInt(Application.VERSION) < 12 Then
                    If AddIns("Analysis ToolPak").Installed <> True Then
                        MsgBox "Please install the Analysis ToolPak & Anlysis ToolPak VBA." & vbCr & vbCr & _
                        "Choose Tools > Add-Ins... " & vbCr & _
                        "then check the box's for Analysis ToolPak & Analysis ToolPak VBA, and click OK."
                        End
                    End If
                End If
             If CInt(Application.VERSION) >= 12 Then
                    If AddIns("Analysis ToolPak").Installed <> True Then
                        MsgBox "Please install the Analysis ToolPak & Anlysis ToolPak VBA." & vbCr & vbCr & _
                        "Choose Microsoft Office Button > Excel Options then click Add-ins...In the manage list select Excel Add-ins and click GO " & vbCr & _
                        "then check the box's for Analysis ToolPak & Analysis ToolPak VBA, and click OK."
                        End
                    End If
                End If
            
            Dim curWorkbook As Workbook
            Set curWorkbook = ActiveWorkbook
            Dim numSelected As Integer
            
                numSelected = WorksheetFunction.CountA(Selection)
                'Axis = WorksheetFunction.CountA(ActiveSheet.Range("R:R"))
                Axis = Sheets("pivot").Range("AF1").Value
                
                Axis_05 = Sheets("pivot").Range("BM5").Value
                Axis_10 = Sheets("pivot").Range("BN5").Value
                Axis_13 = Sheets("pivot").Range("BO5").Value
                Axis_17 = Sheets("pivot").Range("BP5").Value
                Axis_19 = Sheets("pivot").Range("BQ5").Value
                Axis_20 = Sheets("pivot").Range("BR5").Value
                Axis_25 = Sheets("pivot").Range("BS5").Value
                Axis_27 = Sheets("pivot").Range("BT5").Value
                Axis_29 = Sheets("pivot").Range("BU5").Value
                Axis_35 = Sheets("pivot").Range("BV5").Value
                
                
                regretail = Sheets("Analysis").Range("P1").Value
                If Axis < 14 Then
                 MsgBox ("You MUST select atleast 12 weeks in order to RUN this tool.")
                 Else
             
                
                switch_05 = Sheets("pivot").Range("BM3").Value
                switch_10 = Sheets("pivot").Range("BN3").Value
                switch_13 = Sheets("pivot").Range("BO3").Value
                switch_17 = Sheets("pivot").Range("BP3").Value
                switch_19 = Sheets("pivot").Range("BQ3").Value
                switch_20 = Sheets("pivot").Range("BR3").Value
                switch_25 = Sheets("pivot").Range("BS3").Value
                switch_27 = Sheets("pivot").Range("BT3").Value
                switch_29 = Sheets("pivot").Range("BU3").Value
                switch_35 = Sheets("pivot").Range("BV3").Value
               
               
            Sheets("div_minus7").Visible = False
            Sheets("div_minus8").Visible = False
            Sheets("div_minus8").Visible = True
            Sheets("div_minus8").Activate
            MsgBox ("This can take up to 5 minutes. During this time your Excel session may appear to be frozen or your screen may flicker. Please be patient UPTOOL is calculating your output.")
              
            
            Sheets("pivot").Range("J26:P232").Value = ""
              
            Sheets("pivot").PivotTables("PivotTable4").PivotCache.Refresh
            Sheets("pivot").PivotTables("PivotTable5").PivotCache.Refresh
            
            If Application.VERSION <= 11 Then
             If switch_05 = 1 Then
            ' Run div 05 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AG$3:$AG$" & Axis_05), _
            Sheets("Pivot").Range("$AH$3:$AH$" & Axis_05), False, False, , Sheets("Pivot").Range("$J$26") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("AY10").Value = 1
            
            
            If switch_10 = 1 Then
            ' Run div 10 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AI$3:$AI$" & Axis_10), _
            Sheets("Pivot").Range("$AJ$3:$AJ$" & Axis_10), False, False, , Sheets("Pivot").Range("$J$47") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("Az10").Value = 1
            
            
            
            'If switch_13 = 1 Then
            ' Run div 13 regression
            'Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AK$3:$AK$" & Axis_13), _
            'Sheets("Pivot").Range("$AL$3:$AL$" & Axis_13), False, False, , Sheets("Pivot").Range("$J$68") _
            ', False, False, False, False, , False
            'End If
            'Sheets("div_minus8").Activate
            'Sheets("div_minus8").Range("BA10").Value = 1
            
            
            If switch_17 = 1 Then
            ' Run div 17 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AM$3:$AM$" & Axis_17), _
            Sheets("Pivot").Range("$AN$3:$AN$" & Axis_17), False, False, , Sheets("Pivot").Range("$J$89") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BB10").Value = 1
            
            
            If switch_19 = 1 Then
            ' Run div 19 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AO$3:$AO$" & Axis_19), _
            Sheets("Pivot").Range("$AP$3:$AP$" & Axis_19), False, False, , Sheets("Pivot").Range("$J$110") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BC10").Value = 1
            
            
            If switch_20 = 1 Then
            ' Run div 20 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AQ$3:$AQ$" & Axis_20), _
            Sheets("Pivot").Range("$AR$3:$AR$" & Axis_20), False, False, , Sheets("Pivot").Range("$J$131") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BD10").Value = 1
            
            
            If switch_25 = 1 Then
            ' Run div 25 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AS$3:$AS$" & Axis_25), _
            Sheets("Pivot").Range("$AT$3:$AT$" & Axis_25), False, False, , Sheets("Pivot").Range("$J$152") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BE10").Value = 1
            
            
            If switch_27 = 1 Then
            ' Run div 27 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AU$3:$AU$" & Axis_27), _
            Sheets("Pivot").Range("$AV$3:$AV$" & Axis_27), False, False, , Sheets("Pivot").Range("$J$173") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BF10").Value = 1
            
            
            If switch_29 = 1 Then
            ' Run div 29 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AW$3:$AW$" & Axis_29), _
            Sheets("Pivot").Range("$AX$3:$AX$" & Axis_29), False, False, , Sheets("Pivot").Range("$J$194") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BG10").Value = 1
            
            
            If switch_35 = 1 Then
            ' Run div 35 regression
            Application.Run "ATPVBAEN.XLA!Regress", Sheets("Pivot").Range("$AY$3:$AY$" & Axis_35), _
            Sheets("Pivot").Range("$AZ$3:$AZ$" & Axis_35), False, False, , Sheets("Pivot").Range("$J$215") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BH10").Value = 1
            
            
            ElseIf Application.VERSION > 11 Then
             If switch_05 = 1 Then
            ' Run div 05 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AG$3:$AG$" & Axis_05), _
            Sheets("Pivot").Range("$AH$3:$AH$" & Axis_05), False, False, , Sheets("Pivot").Range("$J$26") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("AY10").Value = 1
            
            
            If switch_10 = 1 Then
            ' Run div 10 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AI$3:$AI$" & Axis_10), _
            Sheets("Pivot").Range("$AJ$3:$AJ$" & Axis_10), False, False, , Sheets("Pivot").Range("$J$47") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("AZ10").Value = 1
            
            
            'If switch_13 = 1 Then
            ' Run div 13 regression
            'Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AK$3:$AK$" & Axis_13), _
            'Sheets("Pivot").Range("$AL$3:$AL$" & Axis_13), False, False, , Sheets("Pivot").Range("$J$68") _
            ', False, False, False, False, , False
            'End If
            'Sheets("div_minus8").Activate
            'Sheets("div_minus8").Range("BA10").Value = 1
            
            
            If switch_17 = 1 Then
            ' Run div 17 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AM$3:$AM$" & Axis_17), _
            Sheets("Pivot").Range("$AN$3:$AN$" & Axis_17), False, False, , Sheets("Pivot").Range("$J$89") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BB10").Value = 1
            
            
            If switch_19 = 1 Then
            ' Run div 19 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AO$3:$AO$" & Axis_19), _
            Sheets("Pivot").Range("$AP$3:$AP$" & Axis_19), False, False, , Sheets("Pivot").Range("$J$110") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BC10").Value = 1
            
            
            If switch_20 = 1 Then
            ' Run div 20 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AQ$3:$AQ$" & Axis_20), _
            Sheets("Pivot").Range("$AR$3:$AR$" & Axis_20), False, False, , Sheets("Pivot").Range("$J$131") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BD10").Value = 1
            
            
            If switch_25 = 1 Then
            ' Run div 25 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AS$3:$AS$" & Axis_25), _
            Sheets("Pivot").Range("$AT$3:$AT$" & Axis_25), False, False, , Sheets("Pivot").Range("$J$131") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BE10").Value = 1
            
            
            If switch_27 = 1 Then
            ' Run div 27 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AU$3:$AU$" & Axis_27), _
            Sheets("Pivot").Range("$AV$3:$AV$" & Axis_27), False, False, , Sheets("Pivot").Range("$J$173") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BF10").Value = 1
            
            
            If switch_29 = 1 Then
            ' Run div 29 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AW$3:$AW$" & Axis_29), _
            Sheets("Pivot").Range("$AX$3:$AX$" & Axis_29), False, False, , Sheets("Pivot").Range("$J$194") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BG10").Value = 1
            
            
            If switch_35 = 1 Then
            ' Run div 35 regression
            Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Pivot").Range("$AY$3:$AY$" & Axis_35), _
            Sheets("Pivot").Range("$AZ$3:$AZ$" & Axis_35), False, False, , Sheets("Pivot").Range("$J$215") _
            , False, False, False, False, , False
            End If
            Sheets("div_minus8").Activate
            Sheets("div_minus8").Range("BG10").Value = 1
            
            End If
            
            'create switch to rendering output screen
            'hide loading screen
            Sheets("div_minus7").Visible = True
            Sheets("div_minus7").Activate
            Sheets("div_minus8").Visible = False
            
            Sheets("div_minus8").Range("AY10").Value = 0
            Sheets("div_minus8").Range("AZ10").Value = 0
            Sheets("div_minus8").Range("BA10").Value = 0
            Sheets("div_minus8").Range("BB10").Value = 0
            Sheets("div_minus8").Range("BC10").Value = 0
            Sheets("div_minus8").Range("BD10").Value = 0
            Sheets("div_minus8").Range("BE10").Value = 0
            Sheets("div_minus8").Range("BF10").Value = 0
            Sheets("div_minus8").Range("BG10").Value = 0
            Sheets("div_minus8").Range("BH10").Value = 0
            Sheets("div_minus8").Activate
            
            
            
            Sheets("div_minus7").Visible = False
            
            
            If regretail = 82 Then
            ChDir "U:\Orion\Group " & regretail & "-P\"
            ElseIf regretail = 84 Then
            ChDir "U:\Orion\Group " & regretail & "-P\"
            Else
            ChDir "U:\Orion\Group " & regretail & "\"
            End If
            
            If regretail = 82 Then
                Workbooks.Open Filename:= _
                    "U:\Orion\Group " & regretail & "-P\GROUP " & regretail & " REGRETAIL.XLS"
            ElseIf regretail = 84 Then
                Workbooks.Open Filename:= _
                    "U:\Orion\Group " & regretail & "-P\GROUP " & regretail & " REGRETAIL.XLS"
            Else
                Workbooks.Open Filename:= _
                    "U:\Orion\Group " & regretail & "\GROUP " & regretail & " REGRETAIL.XLS"
            End If
            
            
            
            Dim regWorkbook As Workbook
            Set regWorkbook = ActiveWorkbook
            
            If ActiveSheet.FilterMode Then
                ActiveSheet.ShowAllData
              End If
            
            Range("A:AZ").Select
                Selection.Copy
                
               
                curWorkbook.Activate
                ActiveWindow.WindowState = xlMaximized
                Sheets("Retail").Activate
                Range("A:AZ").Select
                ActiveSheet.Paste
                 Application.CutCopyMode = False
            regWorkbook.Close
            curWorkbook.Activate
               
            
            
            
                Sheets("Optimize").Activate
                ActiveWindow.WindowState = xlMaximized
            End If
            'Application.ScreenUpdating = True
            MsgBox ("UPTOOL has finished building your models. You may now edit & test price points to achieve optimal metrics")
            
            End Sub

            Comment


            • #7
              Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

              as the OP said.. works great on Excel 2003 but gets hung up on Excel 2007 around this code
              Code:
                      Sheets("div_minus7").Visible = False          Sheets("div_minus8").Visible = False          Sheets("div_minus8").Visible = True

              Comment


              • #8
                Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

                anybody?

                Comment


                • #9
                  Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

                  Originally posted by superj707 View Post
                  anybody?
                  i have this issue also, but i fixed it. Not need to change the original source code, just copy everything from original file to new book.
                  you may follow some steps as the below

                  Step1 : Open the Excel file (2003);
                  Step2 : right click the sheets (Sheet1, Sheet2,...etc.) choose "Move or Copy..."(For "select all sheets") you may hold the :"Shift" key and use mouse to click the first and last sheets);
                  Step3 : In the dialog box, Move selected sheets To book : "(new book)" and Tick the "Create a copy" and press "OK" button -> new book will be created;
                  Step4 : Back to the original excel file, press the "alt" + "F11", you will see at least two VBA source codes under the VBAProjects (*original file name) + VBAProjects (Book1.xls);
                  Step5 : Select the Module (e.g.: Module1), drag and drop the module(s) to new book.
                  Step6 : Last, save the excel file.

                  Its works for me which i transferred the source code from 2003 to 2007.

                  Comment


                  • #10


                    Re: Run Time error '57121' Experienced when moving from Excel 2003 to 2007 VBA

                    1. Disable proxy
                    Open the Command Prompt, in order to check, if you are behind the proxy. For this, you have to press the Windows key + R, both at the same time and then type CMD, and press OK.
                    A command prompt windows would be opened, then type the following command netsh winhttp import proxy source=ie and then press enter. If you are still getting “Error writing proxy settings (5) Access is denied.” In that case, error code 57121 could be fixed, if proxy settings in internet explorer should be disabled.
                    2. Improper installation
                    If MS Office is uninstalled or during update if any DLL file goes missing error may occur. In that case, you would get runtime error 57121. Thus fixing the missing DLL could aid you with this runtime error, you have to press Windows key and R at the same time and type regsvr32 dllfix.dll and then hit OK.
                    Last edited by cytop; August 23rd, 2016, 21:14. Reason: Commercial link removed.

                    Comment

                    Working...
                    X