How do I recover a control that's been trapped beyond a Frame edge?

This affects just Frames and I've encountered it only in Excel 11, but since it's obviously a bug it may have been fixed in later versions which I haven't tried. If you use Frames a lot, this WILL eventually bite you.

The Problem

Start with a Frame and any other control contained in that Frame, let's say a Label. Grab the Label by the right edge (crossed arrows) and drag it across the left border of the Frame until you see its dashed outline appear outside the Frame. Then, without releasing the mouse, drag it back toward the border until the outline just disappears and release the mouse at that point.

You'll find your Label has disappeared. It's trapped somewhere out of sight and can't be brought back. Just enlarging the Frame doesn't show it. This works exactly the same way with the top border of the Frame, grabbing the Label by its bottom border to drag it upward and back.

It works similarly with the bottom and right Frame borders, except here you can recover the Label by enlarging the Frame. But not so with the top and left borders.

The missing Label can be tabbed to (it shows in the Properties dropdown), but this doesn't select it, and it can't be deleted because it can't be selected. Clicking on the Label name in the Properties dropdown does nothing.

If you know which Frame has swallowed the Label (as we do in this sample), and if you know which border of that Frame it's under, (as we do in this sample), then there is a way to restore the missing Label. But many times this won't be the case. Unless you actually see the Label disappear it can easily go unnoticed at the time, especially when Frames are overlaying one another.

One way to restore the Label (from under a known border on a known Frame) is to use Stephen Bullen's VBE Tools, (and even without regard to this problem, if you don't have this wonderful Addin installed, you should). Click once on the Frame (that you know is hiding something) and Select All from the Edit Menu (Ctrl + A doesn't work for this). Then hold down Alt-right + arrow (because here it's known that the missing Label is under the LH border) and watch the Label crawl out sheepishly from under the Frame border. This is better than to merely Select All and drag all the controls to the right, because it lets you replace all the other controls to their original positions by just reversing the process (Alt-Left + Arrow) until things look right again. If you need pixel perfect restoration of the other controls to their original position, just count the right-arrow clicks, dispose of the recovered Label, Select All again, and make the same number of left-arrow clicks.

The question

1) How do I find and recover and/or delete a control that's been captured by a Frame in this manner if I don't know which Frame it's under?

Solution

@Chris, thanks for the response, but something I didn't mention is that on my running form, at any given time there may be 50-100 controls that have been moved (temporarily) outside of their containing frames, and your method would find all of these. This would still be ok if I always knew the name of the missing control, and most of the time I will know this, but not always.

However, your method definitely set me on the right track. I started out searching for controls that are outside their containing frame and flush up against it, however it turns out that they aren't flush up against it but rather they're up to 4 pixels distant from the frame (depending on exactly where the control was dropped). And just for added perverseness, the controls that have disappeared under the top and left borders of the Frame are (I think) always outside the Frame, but the controls that have disappeared under the right and bottom borders can be 0-4 pixels either inside or outside the Frame. Pretty ugly.

So, here's what seems to work ok.

Dim ctrl As Control
For Each ctrl In Me.Controls
  With ctrl
    If typeName(.parent) = "Frame" then
       If .Left + .Width > -4 And .Left + .Width < 0 Then Debug.Print .Name & " " & .Parent.Name & " top border"
       If .Top + .Height > -4 And .Top + .Height < 0 Then Debug.Print .Name & " " & .Parent.Name & " left border"
       If .Left > .Parent.Width - 4 And .Left < .Parent.Width + 4 Then Debug.Print .Name & " " & .Parent.Name & " right border"  
       If .Top > .Parent.Height - 4 And .Top < .Parent.Height + 4 Then Debug.Print .Name & " " & .Parent.Name & " bottom border"
    End if
  End With
Next