Private sub won't run on some computers

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have a private sub on "ThisWorkbook" object that works fine on my pc but doesn't on other computers. This sub should "undo" some steps in excel when "Paste" event is found in the undo list. The two computers run the excel on the same network and the office version is the same.

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim UndoString As String
    Dim srce As Range

    On Error GoTo err_handler

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)

    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        
        Exit Sub

    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo


    If UndoString = "Paste" Then

        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Exit Sub

err_handler:

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

I've checked the enable events settings on other computers and it is set to TRUE. To be sure I've also put a private for enabling events.
VBA Code:
Private Sub Workbook_Open()
    Application.CellDragAndDrop = False
    Application.EnableEvents = True
End Sub

The problem sill persists. Do you have any idea on what to look for solving this issue? I've found many discussions regarding this problem but never found a real solution.
Thank you in advance for your time.
Bye
Maurizio
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,140
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To be sure I've also put a private for enabling events.
That's not going to work. If events aren't enabled, that code won't run, since it's an event.

Is the code not running at all, or just not doing what it should?
 

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Private Sub doesn't run at all on some computers. On my computer it runs. I've checked in "immediate" and enable-events setting is set to True on both computers.
 

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
this private sub should allow to paste values only. And it does it on my computer but on others computers it is pasting values and formatting/formulas. So I'm assuming that for some reason it is not working at all on other computers.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,140
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Have you actually checked if it is running at all on the other computers? The code mostly exits silently if there's an error or if the conditions aren't met, so the fact it isn't doing what you expect doesn't necessarily mean it isn't running. I can see for example that you would have issues with non-English versions of Excel.
 

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Wow thank you Rory! Yes you're right! My fault! It's the language.... But now I think that I've to review the sub because I can't catch the language used by all users. The problem is with "Paste", "Auto Fill", "Undo"
Do you have any suggestions on how to change it in order to make it run regardless of language ?
 

maurig

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Found a partial solution using UndoString = Application.CommandBars("Standard").FindControl(ID:=128).List(1) instead of writing .controls(&Undo)

but no idea for now how to replace the string for "Paste".

any ideas?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,140
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try:

Code:
replace(application.CommandBars.FindControl(id:=22).Caption, "&", "")

but I'm not sure how you'd do Auto Fill.
 

Forum statistics

Threads
1,136,729
Messages
5,677,407
Members
419,691
Latest member
jlk3208

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top