Wrong worksheet gets focus after running popup menu macro

ozzyWave

New Member
Joined
Feb 18, 2016
Messages
1
Hi,

We have been using a navigation-aid macro that displays a popup menu (CommandBarPopup). The popup menu shows, as items, a list of sheets, so the user can select one. The onAction property is set to a macro that simply activates a given sheet. The code worked fine on our Excel 2007 files... not working on 2013 version =(

Although the required sheet gets displayed as the active sheet, when the user tries to scroll down by using the scroll wheel or the up/down keys, nothing happens.

The popUp menu macro is assigned to a button on a given sheet (let's say Sheet1). When the user hits the button, the popup menu shows up, and the user can select going to another sheet (let's say Sheet2). Excel changes the focus to Sheet2 (i.e., Sheet2 is displayed), but the scrolling doesn't work!!! However, if the user tries to scroll down for a while, he will realize that the scrolling actually had an effect on Sheet1!! (i.e., when the user goes back to Sheet1, he will notice the cells being shown are down below of the location where the button was placed on Sheet1).
Any advice will be welcomed! Example code shown below.

Code:
Public Const Mname As String = "MyPopUpMenu"

Sub Custom_PopUpMenu_1()
    Dim MenuItem As CommandBarPopup
    
    'Add PopUp menu
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)


        'Add two buttons
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Sheet 1"
            .FaceId = 71
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "GoToSheet1"
        End With


        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Sheet 2"
            .FaceId = 72
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "GoToSheet2"
        End With

    End With

End Sub

Code:
Sub GoToSheet2()
    Sheets("Sheet2").Activate
End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,216,309
Messages
6,130,001
Members
449,551
Latest member
MJS_53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top