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!

 

[sfran]

Is a different (more efficient) way to change the value of a Checkbox Linked Cell?

Maybe another method would work better in my case?

 

[Anthony47]

I created a worksheet with 3 checkboxes (from Modules Controls); I have no problem in assigning and reassigning their linked cells using for example

ActiveSheet.CheckBoxes("NOME1").LinkedCell = "'" & Sheets(Sheets.Count - 1).Name & "'!" & "$D$2"
ActiveSheet.CheckBoxes("NOME2").LinkedCell = "'" & Sheets(Sheets.Count - 1).Name & "'!" & "$D$3"
ActiveSheet.CheckBoxes("NOME3").LinkedCell = "'" & Sheets(Sheets.Count - 1).Name & "'!" & "$D$4"

Would it be possible that some of the checkboxes are "ActiveX"? If you (in the Developer tab) activate the Development mode (not sure about this label… It is in the Controls group, near the Inser icon) you can still set /reset a Modules checkbox, whereas if you click an ActiveX it gets selected.
Or you could just remove the (7?) checkboxes on worksheet "ER&D Estimation Sheet", recreate them and check.

Also, on the hypotesis that the "activesheet" could loose the focus I would try this code variation:

Dim caSheet As Worksheet    '+++ In the Dim area, on top

    Sheets(Sheets.Count - 2).Activate
    Set caSheet = ActiveSheet              '+++
    caSheet.Range("D3:D18").Replace what:="CS3", Replacement:=Sheets(Sheets.Count).Name
   
    caSheet.CheckBoxes("RADFW").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A30"
    caSheet.CheckBoxes("RADApp").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A31"
'etc

I.e.:
-you dim a worksheet object, at the beginning of the macro
-you set this object = the active sheet just after the sheet get activated
-then you use this object rather then "ActiveSheet" in the subsequent code, as shown before.

A less efficient way for working could be this:

    ActiveSheet.Shapes("RADFW").Select
    Selection.LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A30"

    ActiveSheet.Shapes("RADApp").Select
    Selection.LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A31"
    
    ActiveSheet.Shapes("OB").Select
    Selection.LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "A32"
'
'change the other checkboxes
'
ActiveWindow.RangeSelection.Select                          'when completed

This way we could better understand if the problem is with the checkboxes or with their property

I am afraid my creativity is not supporting me much longer; unless you could be able to create a demo workbook, with only the components involved in this problem. That would allow us to emulate the environment and maybe replicate the situation.

I work with Microsoft 365 Italian version; 32 bits; Windows 10 64 bits

Bye

 

[sfran]

For the code that was failing, I changed the "plus sign (+) " to the "ampersand sign (&) " and this seems to have fixed the problem.

Before:
caSheet.CheckBoxes("RADFW").LinkedCell = "'" + Sheets(Sheets.Count - 1).Name + "'" + "!" + "$A$30"

Now:
caSheet.CheckBoxes("RADFW").LinkedCell = "'" & Sheets(Sheets.Count - 1).Name & "'!" & "$A$30"



Thank you for your help. I think I'm all set on this.

 

[Anthony47]

Good to know you got a fix!