Search code examples
excelvbafreezeribboncommandbutton

Run a Macro from Shape or `Button (Form control)` leads to hang the Excel Ribbon, Office 2016 32_Bit


This issue can be produced only on Office 2016.

I have a sheet with name Test , then I hide it manually or by this code:

Sub Make_Sheet_Hidden()
    Sheets("Test").Visible = xlSheetHidden
End Sub

Problem: If the below code Make Visible assigned to Shape or Button (Form control), and run it then used excel Ribbon, like click on Bottom Border this leads to hang the Excel Ribbon. But, strangely If run the below code from Command Button( ActiveX Control) or run from code window , then no problem at all.

Sub Make_Sheet_Visible()
    Sheets("Test").Visible = xlSheetVisible
    Sheets("Test").Select
End Sub

Note: This is the link for the Workbook Link to produce the issue , the sheet Test must first be hidden , then click on Shape or Button 1

Kindly what is the explanation of that issue and How to solve? enter image description here


Solution

  • Note 1: the above issue happens even there is only one workbook opened in Excel.
    Note 2: this issue is not specific with my workbook ,I created new one from scratch and the same issue exists.
    Note 3: that issue is specific with office 2016.
    Answer:
    I replaced Select in the below line with Activate
    Sheets("Test").Select
    So , the good working macro is

    Sub Make_Sheet_Visible()
        Sheets("Test").Visible = xlSheetVisible
        Sheets("Test"). Activate
        End Sub
    

    Actually: I do not know the technical explanation of why using (Activate) instead of (Select) fixed that issue.