Excel: If I open two windows in one workbook, why do macros stop working in the first window?

Question

I am trying to customize a button in Excel to simultaneously display a double view of two separate sheets. This is the code that I have written so far (see below). So far, the code is working. The problem is that there are some activex controls on the worksheet in the top window, and they seem to stop working until the bottom window is closed. Why is this happening, and what can I do to fix it? Thanks.

Private Sub DualViewButton_Click() Dim windowToPutOnTimeline As Window If Windows.Count = 1 Then ThisWorkbook.NewWindow Windows.Arrange xlArrangeStyleHorizontal, True, False, False Set windowToPutOnTimeline = Windows(1) If Windows(1).Top < Windows(2).Top Then Set windowToPutOnTimeline = Windows(2) End If With windowToPutOnTimeline .Activate HorizontalTimelineSheet.Activate .DisplayGridlines = False .DisplayRuler = False .DisplayHeadings = False .DisplayWorkbookTabs = False '.EnableResize = False End With Windows(2).Activate 'go back to the right focus the user expects. Else If Windows(1).Top = Windows(2).Top Then Windows.Arrange xlArrangeStyleHorizontal, True, False, False Else Windows.Arrange xlArrangeStyleVertical, True, False, False End If End If End Sub 

EDIT: if I switch the window that is assigned windowToPutOnTimeline, the problem will disappear. So I essentially worked on the problem, not knowing why it works differently. (see code snippet below)

 With ThisWorkbook Set windowToPutOnTimeline = .Windows(1) Set windowToPutOnDataSheet = .Windows(2) tmp = .Windows(1).Top .Windows(1).Top = .Windows(2).Top .Windows(2).Top = tmp End With 

Answer 1

This behavior is a bug in the ActiveX control.

As work, use the button from form controls, but not the ActiveX button

Using the “Forms” button, you will need to add a module, declare Sub with your code and assign Sub as an action macro to your button (as indicated for placing your code in an ActiveX button click event)

I tried this in Excel 2007, it seems to work fine - the button appears and works in both windows