Find lost check box control in workbook

[Guest]

A reply to a similar question 2 years ago suggested that you can find lost
controls in Excel workbooks by brining up the Visual Basic editor and
dropping down the list in the Properties window.

When I do this, I can see objects like "Sheet1" but none of the controls
that are on that sheet. The drop down is limited to at most one entry, no
matter what I've got selected in the Project window.... except when I select
"ThisWorkbook" and then I can see only the list of sheets in the dropdown in
Properties.

I've also searched for GOTO CONTROL and many seemingly related terms, but it
appears that nobody else has this problem. Please tell me there's a better
way than right-clicking every control on the sheet until I find the one that
has the name I'm looking for. (names like "Check Box 268")

 

[Guest]

When you have a lot of shapes on a worksheet to manage it's a good idea to
add the Select Multiple Objects control to the menu bar. This control brings
up a dialog that displays all the objects on the worksheet in checkbox form.
You can select any one you want using the dialog. To add it to a toolbar:
1. Right click any toolbar
2. Select Customize
3. Activate the Commands tab
4. Select Drawing from the Category list
5. Drag the Select Multiple Objects icon to the toolbar

If a shape gets lost you can find it by running something like this:

Sub FindShape
ActiveSheet.Shapes("Check Box 268").TopLeftCell.Select
End Sub

You can also select all objects on a worksheet through Edit > Go to >
Special button > Objects.

Regards,
Greg

 

[Guest]

Greg:

I didn't realize that a "Check Box" was a "Shape".

The code you provided makes sense, but when I run it for a known and visible
Check Box, it sets focus to the cell above it. That, I can live with. Next,
I will enclose this in a loop so it will hit all the sheets so I don't have
to run it manually for each.

Maybe I'm missing something, but I had already used the GoTo|Special to
highlight all the objects and I don't see how this could help find a specific
Check Box by its name from the hundreds of checkboxen in this workbook. Or
were you just thinking that this would help reveal controls that had been
over-laid?

So, Excel has no simple "find" dialog that can find a control on a sheet?

Do you know why the dropdown in the Properties window never shows "shapes"?

I am the math. You know what to do.

 

[Guest]

The Select Multiple Objects control that I mentioned will bring up a dialog
that lists all the shapes on the sheet unless they were made invisible (the
shapes collection includes all objects). Beside each shape name in the list
is a checkbox. If you look for the desired shape in the list and check it, it
will be activated after you close the dialog. This will visually identify it
from amongst many. However, if it is off screen, it won't take you to it.

If you run the simple code I gave you then it will take you to it by
selecting the cell immediately under its top-left corner. The window will
scroll to the selected cell. When you say the code sets focus to the cell
above it, this isn't actually true. It selects (sets focus) to the cell
immediately under its top-left corner which will often appear to be the cell
above it.

When I mentioned the Edit > Go to > Special button option, this was intended
only for general information. It's often useful for finding lost shapes when
there are only a few on the sheet. For example, when rows are deleted,
drawing objects within these rows (if set to move and size with cells) don't
disappear but shrink to zero height. Therefore, you can't find them or don't
know they're there. When you select it this way you will see the sizing
handles even though it has zero height. Even if it is off screen, it will
scroll to its location. So this is a very quick way to resolve this sort of
thing.

So, Excel has no simple "find" dialog that can find a control on a sheet?

The toolbar control I mentioned or VBA code is the only way I know of. The
Find dialog available through the Edit menu will only find text within cells.

Do you know why the dropdown in the Properties window never shows "shapes"?

I don't know what you mean by Properties window. I suspect you meant the
dropdown at the top-left of the worksheet's code module. This will list only
controls placed on the worksheet from the Control Tollbox toolbar. These are
Active-X controls (also called OLE objects) or non-native controls. IMO, you
should avoid them when there is a suitable alternative from the Forms toolbar
because they are buggy due to the linking process. Also, often other
computers won't have their source files resulting in much grief if you're
trying to disseminate a project. The calendar control is a good example of
this. I've had to include a patch with something I wrote because of this.

If you meant the Properties window for the worksheet (View > Properties
window from the VBE menu bar), this only lists properties for the worksheet
and does not list child objects.

Regards,
Greg

 

[Guest]

Greg:

Thanks for expounding on the subtleties of the issues involved in my
questions. This caused me to look at the Select Multiple Objects dialog.
Previously, I had followed your advice and instructions for making it
available on the toolbar, but I had neglected to subsequently try it out.
Though it's not alphabetized, it still helps a lot.

And of course, you're right that the code you provided does select the cell
that contains the top-left pixel of the named control. I had used this
feature in a macro that sets the Linked Cell for non-Active-X dropdowns, but
I had not considered how it would look to see the relevant cell highlighted.
Now I see.

The spreadsheets I'm dealing with were created by somebody else, so I didn't
get to decide which components they used. I was quite familiar with some of
the differences between controls from the Controls Toolbox menu and those old
compatibility-mode controls from the Forms menu, but I did not realize that a
blanket move to the Active-X controls was not necessarily a good idea.
Having the controls appear in the Properties window of the code module would
have been handy thing because then the prior advice I read on the "lost
controls" topic would have worked.

In any case, I will soon eliminate all of these problems by doing what I've
done many times over the last two decades: Write an app that replaces a bunch
of spreadsheets. This ends up preserving the corporate assets in a manner
that is more transferable to people beyond the one who created the
spreadsheet.