3

Not a duplicate because the issue described here happens despite having deleted the *.exd files as suggested in the answer to Excel renaming Activex Controls on other computers and elsewhere.

One particular machine on our network (let's call it "Computer 2") spuriously and silently changes the code name of ActiveX command buttons placed in Excel workbooks. Whatever the (Name) property of a button was before, it returns it to the default CommandButton* scheme. (CommandButton1, CommandButton2... etc.)

Witness the screenshots below. The code name of btn2 changes to CommandButton1 when opened on Computer 2.

Why? How do I fix this?

I can even have the exact same workbook opened from the same Book1.xlsm file on a network drive simultaneously on both machines (one of them read-only, obviously). 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, because they no longer trigger their intended event code. In the example below, btn2 used to call Private Sub btn2_Click() from the sheet module and execute the code in that Sub. But on Computer 2, the button is no longer named btn2, so it doesn't trigger that event; it does nothing — or worse, if there happened to be a button called CommandButton1 before, it triggers that unrelated event.

Workbook opened on Computer 1:

enter image description here

Exact same workbook, but this time on Computer 2:

enter image description here

Now, this has happened to me before. Once or twice over the years, on a couple different machines, all my commandbuttons got renamed like that. But I've never been able to reproduce this, and I thought, corrupted workbook, no big deal.

But this happens consistently on Computer 2, every single time.

Non-ActiveX, Form Control buttons, such as "Form Button 1" in the example above, are unaffected by this issue. An obvious but tedious fix would be to get rid of all my ActiveX buttons (as suggested in this answer) and convert them into e.g. Form Control buttons, but the aim is to avoid this nuclear option.

Community
  • 1
  • 1
Jean-François Corbett
  • 34,562
  • 26
  • 126
  • 176
  • Could you post the code used to create buttons? you could try removing the msforms from computer 2? Just something to try and it wont harm anything. http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled – 99moorem Jul 03 '15 at 08:25
  • @99moorem: I am aware: http://stackoverflow.com/a/27500258/119775 Unfortunately that isn't it. No code was used to create the buttons; I just added them manually from Developer > Insert. – Jean-François Corbett Jul 03 '15 at 08:27
  • @Jean-FrançoisCorbett [Certain MS security updates may cause such harm.](https://support.microsoft.com/en-us/kb/3025036/en-us). Can you check the machine for latest updates? Does it run the same image of software updates as the other machines? Same specs? OS, MS Office version etc? Also do check if Excel is creating new controls rather than renaming what you just changed. – bonCodigo Jul 03 '15 at 10:06
  • [Here's another post on the subject.](http://stackoverflow.com/questions/28074908/excel-renaming-activex-controls-on-other-computers). I didn't intend a close vote though. – bonCodigo Jul 03 '15 at 10:08
  • @bonCodigo: This isn't a duplicate, because the issue occurs despite having applied the fix suggested [in that answer](http://stackoverflow.com/a/28111733/119775) and which I was aware of already. Close votes can always be retracted... – Jean-François Corbett Jul 03 '15 at 10:57
  • Are there any differences between computers? Especially in hardware, OS and Offcice? The "feature" looks like recompiling the internal code of the button that causes regenerating its name and sets it to default. To investigate this, compare `/xl/ActiveX` parts of both documents (open them as zip archives) after opening, saving then closing. To uncompress and read files like `activeX1.bin` of the part, begin from [Office VBA File Format Structure](https://msdn.microsoft.com/en-us/library/cc313094%28v=office.12%29.aspx). – Aleksey F. Jul 17 '15 at 15:46

3 Answers3

0

First, I have very limited knowledge of VBA.

Second: I have no idea why this happens, but I have an idea for how to fix it... In workbook_open could you run code that renames any CommandButtons to your desired name? It would be annoying, sure, but it would be at least a temporary fix until someone can find out why on Earth this happens! :)

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
seadoggie01
  • 481
  • 3
  • 17
0

A colleague found out that this can occur if there is a mix of Office 2010 and Office 2013 products installed on a machine. The procedure to fix this is:

  1. Download these patches:

  2. Close all Office programs (including Lync if you have it).

  3. Install the Office 2010 patch.
  4. Install the Office 2013 patch.
  5. Remove *.exd files from your profile and for all other user profiles. For instructions, see this Stack Overflow answer and this Microsoft solution.
  6. Restart your machine.
Jean-François Corbett
  • 34,562
  • 26
  • 126
  • 176
0

Since none of any recommended solution worked for me, I am assuming some people still have that problem and are interested in the fix that helped me:

Make your button names shorter!

Sounds stupid, but I found out that button name length has it's limits.

Try this out by making a new sheet with one ActiveX CommandButton in it. Then add this code and execute it repeatedly 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 yeah, I found this fix because I was desperate enough to try seadoggie's solution, which failed because you can't read a button's caption to identify which name the button should get. :)

Aroddo
  • 93
  • 1
  • 5