Long Worksheet ActiveX Control Names

Here’s a nice little bug for you try out. I’ve written the below step-by-step guide for Excel 2010 but it’ll ‘work’ in earlier versions too.

• Create a new workbook
• Insert an ActiveX commandbutton onto Sheet1 using Developer tab > Insert
• Staying in design mode and with the commandbutton selected, change its name in the Name Box (to the left of the formula bar) to cmdHello
• Double left-click on the commandbutton to create its Click() event handler and add the below code:

Private Sub cmdHello_Click()
    MsgBox "I am cmdHello"
End Sub

• Turn off design mode and left click on the commandbutton to verify that it works. You should get a message box showing the commandbutton’s name:

Okay, so far so good. Next:

• Enter design mode again, right-click on the commandbutton > properties
• In the properties window change the name to cmdMyNameIsALittleBitLongerThanItShouldBe
• Close the properties window and then double left-click on the commandbutton. You should get a new Click() event handler in the VBE which reflects the updated name.
• Add the following code to the Click() event handler:

Private Sub cmdMyNameIsALittleBitLongerThanItShouldBe_Click()
    MsgBox "I am cmdMyNameIsALittleBitLongerThanItShouldBe"
End Sub

• Close the VBE and exit design mode
• Left-click on the commandbutton to verify that the code works. You should get a message box showing that the commandbutton’s new name is cmdMyNameIsALittleBitLongerThanItShouldBe:

• At this stage you may notice that the name of the commandbutton hasn’t changed in the Name Box
• Save the workbook as an .xlsm (or .xls if you’re using Excel 2003 or earlier), close and re-open it
• Click on the commandbutton to see what happens

Lessons to be learnt?

If possible, avoid embedding ActiveX controls on worksheets. There are lots of bugs out there which you may fall foul of. These bugs have been around for many versions of Excel so I don’t see them being fixed any time soon. If you must embed an ActiveX control on a worksheet then keep its name to 32 characters or less.

Advertisements