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.
I’m impressed. When I first realized there was no built in VB tearaway I was quite bummed – but neither bummed nor talented enough to invent my own.
One problem I did notice is that if I close the “parent” toolbar and then click a button in the tearaway I get a run time error “Method Top of object Commandbar Button failed.” (xl2k)
As always, thanks for your very informative blog.