Daily Dose of Excel

Jon commented in Undockable Toolbars that:

I agree that it’s a neat trick, and wish that Excel had some way for me to program my own tear-away menus.

Well, it sounded like a fun project. As it turns out, it wasn’t very fun at all. There’s a lot of stuff going on with those toolbars. I couldn’t replicate the built-in ones exactly, but I got close. If you want to give it a try, download FakePopup.zip and tell me how to make it better.

If you’re following along at home, here’s what’s happening: Click the button to run CreateFakePopup which creates two toolbars - one to hold the fake popup control and one to be the tear-away popup. It then assigns the control and the toolbar to properties of a class. Another class holds all the buttons on the fake popup.

When you click the control, the toolbar is made visible in a position that attempts to mimic a real popup. If you click the control again it disappears. Clicking any buttons on the popup will make it disappear if it’s in the original popup position. However, since it’s a just a toolbar, you can move it around and dock it like any toolbar. If it’s moved from its original position, clicking the buttons keeps it visible.

The part that was surprisingly difficult was positioning the popup. There’s all kinds of things to consider like where the toolbar is docked and how close it is to the edge of the screen. I ended up using a couple of properties to determine where the popup should show. I based it on such numbers as Application.Width and Application.Height, but those are not ideal in the sense that it doesn’t work the same as built-in popups. The Application (and it’s dimensions) aren’t on the same “grid” as the commandbars, I guess. I think API’s would be necessary to get it right, but this seems good enough.

Here’s one of the properties called RelativeTop that determine where the popup should show:

Property Get RelativeTop() As Double

    Dim dTop As Double
    Dim bGoesDown As Boolean
    Dim oButton As CommandBarButton
    Dim oBar As CommandBar
    
    Set oButton = Me.ActionButton
    Set oBar = Me.CommandBarObject
    
    If oButton.Top + oButton.Height + oBar.Height _
        > Application.Height Then
        
        dTop = oButton.Top - oBar.Height
        bGoesDown = False
    Else
        dTop = oButton.Top + oButton.Height
        bGoesDown = True
    End If
    
    If oButton.Parent.Position = msoBarLeft Or _
        oButton.Parent.Position = msoBarRight Then
        
        If bGoesDown Then
            dTop = dTop - oButton.Height
        Else
            dTop = dTop + oButton.Height
        End If
        
    End If
    
    RelativeTop = dTop
    
End Property

Another problem I had was with the Class_Terminate event. I kept getting errors the second time I clicked the button to create the toolbars. I think the original instance of the class (from the first click) wasn’t terminating properly, likely due to the relationship with the other class. Instead of figuring it out, I took the chicken’s way out and just ran the Terminate event explicitly. Hey, nobody’s perfect.

Ideally, you could drop these two classes into any project and create a fake popup. It needs some better error checking, though.