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

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , . Bookmark the permalink.

2 Responses to Long Worksheet ActiveX Control Names

  1. Hans Peter says:

    Interesting – I was indeed puzzled by this error until I tried to shorten the name a bit and it suddenly began to work. So 32 char is the limit. Good to know thanks

    Like

  2. Pingback: ActiveX Controls Not Working On Worksheets | RAD Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s