International error running macro's

[sfran]

I have created a sheet that contains several different macros and have shared this with my global team. It seems that the file is able to be opened and used without issue in North America, however the same file is not able to run any macro's in other regions... specifically Italy (all experience the same error, not just one person).

I have pasted the error below.. but I am told the translation means "Object is disconnected from related clients". Would anyone know why this error is happening and how to get around it?

Thank you!

 

[Anthony47]

Can you share a workbook that should fail?

Ciao

 

[sfran]

Unfortunately, the workbook contains sensitive company information that I am not able to send out. I get errors if I try to remove that information and send to you. Is there any other information/details I can share with you.. other than sending you the complete workbook?

 

[Anthony47]

I had in mind a sample workbook with one of the failing components; you wrote that they are not able to runa any macro, so i thought it was easy to prepare...

Random questions:
Which object get disconnected? Which vba instructions are involved? Which type of vba module (class or standard) contains the code? Are all the objects fully addressed (ie workbook, worksheet, etc etc)? Do you runa the same version of Office? Have the workbook been tested in the minumum system configuration (ie: only excel running, only that one workbook)?
But without a running case it is hard to rise the key questions...

 

[sfran]

I have pasted one macro that is experiencing the error. The error seems to occur when trying to change the checkbox cell reference formula. I have indicted this in the bold red font in the code below (1/2 way down the code). Again, this same code works when running the macro in North America (Excel 2016), but fails when running it from Excel 2010 in Italy (Excel 365). One interesting observation is that it changes the formula for the checkbox "RADFW", but fails for "RADApp"... both of these checkboxes are identical in logic/structure.

Sub ProjectCopy()
Dim i As Long, LastRow As Long, Ws As Worksheet
Dim show As Integer

Sheets("Project Description").Activate
LastRow = Worksheets("Project Description").Cells(Worksheets("Project Description").Rows.Count, "A").End(xlUp).Row
totalsheets = Worksheets.Count

'checkbox testing
Dim cb As CheckBox

intCount = 0
show = 1

Dim chk As CheckBox
Dim TBMFW As CheckBox
Dim RADFW As CheckBox
Dim TBMApp As CheckBox
Dim RADApp As CheckBox
Dim OB As CheckBox
Dim OnlyOB As CheckBox
Dim TBMWLD As CheckBox


If WorksheetFunction.CountA(Range("A8:A100")) = 0 Then
MsgBox ("No Features Listed")
Else


If totalsheets > 6 Then
    MsgBox ("Project already started. Please select 'Add' feature to continue adding features")
Else

    show = 0
    If show = 0 Then
        ActiveSheet.Shapes("FeatTabs").Visible = False
   
For Each Ws In ActiveWorkbook.Worksheets
    If Ws.Name Like "CS*" Then Ws.Visible = True
Next Ws

For i = 8 To LastRow
    Sheets("Project Description").Activate
    Sheets("ER&D Estimation Sheet").Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Project Description").Cells(i, 1)
    ActiveSheet.[A1] = ActiveSheet.Name
    ActiveSheet.Range("A1").Select
   
'create calc sheet "CS2" for each feature
    Sheets("Project Description").Activate
    Sheets("CS2").Copy After:=Worksheets(Sheets.Count)
   
' change CS2 formulas
    Sheets(Sheets.Count).Select
    ActiveSheet.Range("B2:B9").Replace what:="ER&D Estimation Sheet", Replacement:=Sheets(Sheets.Count - 1).Name
    Sheets(Sheets.Count - 1).Activate
    ActiveSheet.Range("B2:B21").Replace what:="CS2", Replacement:=Sheets(Sheets.Count).Name
   
'create calc sheet "CS3" for each feature
    Sheets("CS3").Copy After:=Worksheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    ActiveSheet.Range("E41:E41").Replace what:="CS2", Replacement:=Sheets(Sheets.Count - 1).Name
    ActiveSheet.Range("B36:N42").Replace what:="ER&D Estimation Sheet", Replacement:=Sheets(Sheets.Count - 2).Name
   
' change formulas on ER&D Estimation sheets
    Sheets(Sheets.Count - 2).Activate
    ActiveSheet.Range("D3:D18").Replace what:="CS3", Replacement:=Sheets(Sheets.Count).Name
   
    ActiveSheet.CheckBoxes("RADFW").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A30"
    ActiveSheet.CheckBoxes("RADApp").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A31"
    ActiveSheet.CheckBoxes("OB").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "C31"
    ActiveSheet.CheckBoxes("TBMFW").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "B30"
    ActiveSheet.CheckBoxes("TBMApp").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "B31"
    ActiveSheet.CheckBoxes("OnlyOB").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "C30"
    ActiveSheet.CheckBoxes("TBMWLD").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "E31"

    ActiveSheet.Range("B10").Select
    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="='" & Sheets(Sheets.Count).Name & "'!B1:M1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Sheets("Project Description").Activate

Next i

    Sheets("Project Description").Activate
    Range("B1").Font.Bold = True
   
Worksheets("ER&D Estimation Sheet").Visible = xlSheetHidden

Sheets("Project Description").Activate

With Sheets("Project Description").Range("A7:AF7").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
End With

With Sheets("Project Description").Range("AL6:AL" & LastRow).Borders
        .LineStyle = xlNone
End With
   
For Each Ws In ActiveWorkbook.Worksheets
    If Ws.Name Like "CS*" Then Ws.Visible = xlHidden
Next Ws

ActiveSheet.Shapes("ProjectTotal").TextFrame.Characters.Text = "$0"
ActiveSheet.Shapes("Labor").TextFrame.Characters.Text = "$0"
ActiveSheet.Shapes("ED&D").TextFrame.Characters.Text = "$0"

ActiveSheet.Shapes("AppTime").TextFrame.Characters.Text = "0 months"
ActiveSheet.Shapes("TBMTime").TextFrame.Characters.Text = "0 months"

ActiveSheet.Shapes("AppTime").Fill.ForeColor.RGB = RGB(91, 155, 213)
ActiveSheet.Shapes("AppTime").Line.ForeColor.RGB = RGB(65, 113, 156)

ActiveSheet.Shapes("TBMTime").Fill.ForeColor.RGB = RGB(91, 155, 213)
ActiveSheet.Shapes("TBMTime").Line.ForeColor.RGB = RGB(65, 113, 156)

Sheets("CS4").Range("E9") = "0"
Sheets("CS4").Range("C10") = "0"
Sheets("CS4").Range("F10") = "0"
Sheets("CS4").Range("N9") = "0"
Sheets("CS4").Range("L10") = "0"
Sheets("CS4").Range("O10") = "0"

Sheets("CS4").Range("A11:N100").Delete

MsgBox ("Project Started")

End If
End If
End If


   
End Sub

 

[Anthony47]

Some more questions, unfortunately...

How is the Sub ProjectCopy started (a worksheet event macro? a command from userform? an ActiveX or Module button? Via ....?
How has it been determined that the error lie in the red coloured code line?
Are those Checkboxes "Modules" or "Activex"?
Which type of vba module (class or standard) contains the code?
Has the worksheet "ER&D Estimation Sheet" (the one that is duplicated and is the Activesheet when that macro is run; right?) any Event macro?
Same question about worksheet CS2 (the one that, after the duplication contains the linked cells of the checkboxes; right?)
Which Excel version is used in Italy: Office 2010 or Microsoft 365?

Bye

 

[sfran]

I will try my best to answer your questions:
- The macro is invoked by selecting a shape in a worksheet (shape assigned to macro).
- I have stepped through the vba code and the code line I identified is where it encounters the error
- Not sure on your question related to checkbox modules or ActiveX... I know they are not ActiveX, so I am assuming then they are "modules"
- Not sure on the type of vba module.. how do I confirm this? The macros are contained in the "Microsoft Excel Objects" section in the VBAProject. They are not contained in the "Modules" section. Not sure if I answered your question correctly.
- Yes, the ER&D Estimation Sheet is the Activesheet when the macro is run. Please keep in mind, this works fine when I run it. It fails when my colleagues in Italy run it.
- Yes, same for CS2.
- Italy is using Microsoft Office 365

Thank you,

 

[Anthony47]

The macros are contained in the "Microsoft Excel Objects" section in the VBAProject. They are not contained in the "Modules" section. Not sure if I answered your question correctly.

It is clear, the code is in a worksheet Class module. Please read the name of the module, on the upper row of the vba window (it says "Microsoft Visual Basic, Application edition - Name of the workbook - [Name of the Module])
Move any macro that don't belong to any worksheet event ( see Worksheet object events) to a standard module (use Menu /Insert /Module to create them)

Yes, the ER&D Estimation Sheet is the Activesheet when the macro is run. Please keep in mind, this works fine when I run it. It fails when my colleagues in Italy run it.

Ok, and do this sheet and CS2 contain any event macro? Worksheet_SelectionChange, Worksheet_Change, Worksheet_Activate and similar? If Yes, it could be better if you disable events (Application.EnableEvents = False) before starting the operations that don't NEED the events active, and reenable them at the completion of the task (Application.EnableEvents = True).

Bye for now

 

[Anthony47]

And remove all those Dim xxx As Checkbox at the beginning of the macro: I don't see where they are used and the name of thos eobjects conflicts with the name of the controls

 

[sfran]

Ciao... I have moved the macro to its be a standard module.

The name of the macro is: Response Template_.xlsm - [Module4(Code)]

In addition, I have also removed all the Dim xxx As Checkbox and have applied "Application.EnableEvents = False", just to be safe.

The error is now:

Seems to still be something with the 'LinkedCell' of the CheckBoxes. When you click on 'Debug' it stops at the exact same code line as the previous error.

thanks,