Results 1 to 6 of 6

Thread: Microsoft Excel365 ActiveX controls not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Microsoft Excel365 ActiveX controls not working

    Hi All,
    I'm trying to replace some ugly grey buttons in a document with ActiveX buttons. If I add a button, put some code behind it, exit Design mode and click on the button, rather than executing code a smaller copy of the button appears above and to the left of the button.
    If I then save the document and reopen it, sometimes the button starts working, but usually not.
    Heres an example of the code I'm trying to run:

    Code:
    Private Sub CommandButton1_Click() 
    Sheets("Sheet2").Visible = True 
    Worksheets("Sheet2").Activate 
    End Sub

    I've tried deleting all exd files from my computer. Is this a known issue and is there a work around? I see Microsoft broke ActiveX controls in Excel back in 2014, but I would have thought 4 years later they would be fixed?
    Last edited by revelation.now; Feb 13th, 2019 at 07:02 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Microsoft Excel365 ActiveX controls not working

    What version of Excel are you using? There is no such version as Excel 2014, by the way.

    ActiveX controls do not work in Mac Excel, and most people, myself included, will advise you to use controls from the Form menu instead of ActiveX because of reliability.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    New Member
    Join Date
    Dec 2009
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Microsoft Excel365 ActiveX controls not working

    Quote Originally Posted by Jon Peltier View Post
    What version of Excel are you using? There is no such version as Excel 2014, by the way.
    I'm using Office365 Pro (as per the subject) which basically a 'cloud' version of the old 'year numbered' versions of Office. There is an Office 365 for Mac which you might be more familiar with.

    Current version is 1901 (Build 11231.20130)

    I mentioned 2014 in reference to the year in which I can see Microsoft last broke ActiveX, and that applies to Office 2007, 2010 and 2013 which were the available versions of Office at the time (which was 2014). Unfortunately most of those fixes won't apply given that Microsoft never made a fix for this for later versions of Office (such as 2016 or 2019 or Office365)
    Last edited by revelation.now; Feb 13th, 2019 at 09:48 PM.

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Microsoft Excel365 ActiveX controls not working

    Sorry, I missed the reference to Office 365 in the subject line. That's what I'm using too (to be precise, build 11330.20014).

    ActiveX controls were badly broken in Excel 2007, which was more an issue with Office 2007 rather than ActiveX, but by SP2 they weren't quite so bad. But in general, as I noted, ActiveX controls can be rather flaky, despite nominally looking nicer and being more full-featured than Form controls. This was true in Excel 2003 and earlier, and it's been true in Excel 2010 and later. But when my clients use them, then so do I.

    And so I duplicated your situation. I made a workbook with Sheet1 and Sheet2, then I hid Sheet2, and created CommandButton1 on Sheet1. I right clicked on the button, and inserted your two lines of code into the procedure stub that Excel inserted in the Sheet1 code module.

    Then, the moment of truth. I turned off Design Mode, then clicked the button. Sheet2 became visible and then was activated.

    I can think of a couple things that may have gone wrong. Make sure the button name matches the procedure name. And don't type the entire procedure yourself, but use a right click to view the code, which inserts the appropriately named procedure in the appropriate code module, usually the module behind the sheet that contains the button.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    New Member
    Join Date
    Dec 2009
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Microsoft Excel365 ActiveX controls not working

    Quote Originally Posted by Jon Peltier View Post
    I can think of a couple things that may have gone wrong. Make sure the button name matches the procedure name. And don't type the entire procedure yourself, but use a right click to view the code, which inserts the appropriately named procedure in the appropriate code module, usually the module behind the sheet that contains the button.

    * the code has been auto gen'ed, I've just entered the two statements
    * the function matches the name of the button given that it was auto-gen'ed.

    Again, I can sometimes open this 'sample' document and have the ActiveX control work. I don't need to change anything, maybe just restart the computer.

    I was handed this task by a less experienced developer who had the same problem on his computer, and our computers come from different images and manufacturers - the only consistency is that we are both using Office365 rather than a retail release, and we're both using Windows 10.

    I've attached an image to illustrate this a bit better. In the linked document there is 1 button. You can see 2 buttons, but thats what happens most of the time when these ActiveX buttons are clicked.

    Unfortunately, there appears to be a bug in the mrexcel.com forum that prevents the image link embedding from working. Here is the raw URL: https://www.dropbox.com/s/e25i8wfzej5bvnx/brokenexcel.png?dl=0
    Last edited by revelation.now; Feb 13th, 2019 at 11:15 PM. Reason: Appears to be a bug in this forum - images don't attach

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Microsoft Excel365 ActiveX controls not working

    Yeah, that's a strange one. Are there lots of ActiveX buttons? I would try deleting them, saving and closing the workbook, then adding them back. Kind of like turning them all off and on.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •