Chris directed me to an interesting thread at Mr. Excel regarding inpsecting the properties of a cell. There’s two parts to this thread: Viewing the properties and looping through the properties. I’ve tried to create a program that loops through the properties by reading the library. I always start at Chip’s site and his TypeLib Utilities download. I pour through the code getting ever closer to understanding it. Then my head explodes and I give up. I’m not saying it’s possible even if I did understand it. Just that it seems like a good place to start, and that’s as far as I get.
You could easily put all the properties of a cell into a message box, but you’d have to hard code all the properties, which brings us back to looping through the properties. What would be nice, I think, is if the Properties dialog (Control Toolbox > Properties) had a Selection object choice in the dropdown. Then, the properties of whatever you had selected would show in that box, be it a range or a shape or whatever. Alas, it doesn’t.
The only other convenient place to find the properties, that I couldn’t think of, is the Locals Window. To that end, I wrote this:
Dim cell As Range
If TypeName(Selection) = “Range” Then
Set cell = Selection
Application.CommandBars.FindControl(, 1695).Execute ‘show the vbe
Application.VBE.CommandBars.FindControl(, 2555).Execute ‘show the locals window
Stop ‘keep the variable in scope
It may be possible to 1) make “cell” global so that you don’t have to Stop the macro and 2) use API’s to show the Locals Windows without showing the VBE as if it was a child window of the main Excel application. I leave that to you.