Parasitic change of the codename of the ActiveX control button
Not duplicated because the problem described here happens even though they deleted the * .exd files as suggested in Excel's answer , renaming ActiveX controls on other computers and elsewhere.
One particular machine on our network (let's call it "Computer 2") is falsely and silently changing the codename of ActiveX command buttons placed in Excel workbooks. Whatever the (Name) property of the button is, it reverts it to the standard schema CommandButton*
. ( CommandButton1
, CommandButton2
... etc.)
Record screenshots below. The codename btn2
changes to CommandButton1
when opened on computer 2.
Why? How to fix it?
I can even have the same workbook open from the same Book1.xlsm file on a network drive on both machines at the same time (one of them is obviously read-only). Looking at both screens at the same time, the button names are different! Computer 2 changed it.
This, of course, breaks the functionality of the buttons as they no longer fire their intended event code. The example below is btn2
used to call Private Sub btn2_Click()
from a sheet module and execute code in that Sub. But on Computer 2, the button is no longer called btn2
, so it doesn't raise this event; it doesn't do anything - or worse, if there was a CommandButton1 before, it fires this unbound event.
Workbook open on computer 1:
Exact workbook, but this time on computer 2:
Now this has happened to me before. Once or twice over the years, on several different machines, all of my command buttons have been renamed. But I could never reproduce it and I thought it was a broken book, never mind.
But this happens sequentially on computer 2 every time.
Inactive form control buttons such as "Form Button 1" in the above example are not affected by this issue. An obvious but tedious solution would be to get rid of all my ActiveX buttons (as suggested in this answer ) and convert them to eg. Form control buttons, but the goal is to avoid this nuclear option.
source to share
The employee learned that this can happen if a mixture of Office 2010 and Office 2013 products is installed on the machine. Correction procedure:
-
Download these patches:
- Office 2010 Patch: http://support.microsoft.com/kb/2553154/EN-US
- Patch Office 2013: http://support.microsoft.com/kb/2726958/EN-US
-
Close all Office programs (including Lync, if you have one).
- Install the Office 2010 patch.
- Install the Office 2013 patch.
- Remove * .exd files from your profile and for all other user profiles. See this answer and this Microsoft solution for instructions .
- Restart your computer.
source to share
First, I have very limited knowledge of VBA.
Second: I have no idea why this is happening, but I have an idea how to fix it ... In workbook_open, can you run the code that renames any CommandButtons to the name you want? It would be a shame, of course, but it would be at least a temporary solution until someone knows why on Earth this is happening! :)
Sub Not_Workbook_Open()
Dim btn As OLEObject, increment As Integer
increment = 1
For Each btn In Sheets("Sheet1").OLEObjects
btn.Name = "btn" & increment
increment = increment + 1
Next
End Sub
source to share
Since none of the recommended solutions worked for me, I guess some people still have this problem and are interested in a fix that worked for me:
Make your button names shorter!
Sounds silly, but I found out that the length of the button name is limited.
Try this by creating a new sheet with one ActiveX CommandButton. Then add this code and execute it multiple times by pressing F5:
Public Sub test()
For Each o In ActiveSheet.OLEObjects
Debug.Print Len(o.Name); Tab(5); o.Name
o.Name = o.Name & "X"
Next
End Sub
You will notice that the code stops executing after
31 CommandButton1XXXXXXXXXXXXXXXXX
And yes, I found this fix because I was desperate enough to try seadoggie's solution, which failed because you can't read the title of the button to determine what name the button should get. :)
source to share