Make the macro display the cell addresses affected.

**leaning** · 02-14-2011, 02:41 PM

Hello!

I tried to modify this to get the AffectedCell.Address displayed in the msgbox, but only succeed in getting the ActiveCell.Address (and just "cell" doesn't work). What else do I need to add to this?

Please Login or Register  to view this content.

Thanks for your help!

VR/Lost

**MarvinP** · 02-14-2011, 03:04 PM

Hi learning,

Go to VBA and View the Object Browser. Change to Excel and search for the word "Affected". There is no object or method with that word in it. What do you mean when you say "The Affected Cell"?

If an entire worksheet is dependent on a single cell and you change that one cell's value, should the message box list all cells that change, based on formulas? Is this what you mean by Affected?

**nimrod** · 02-14-2011, 03:27 PM

Please Login or Register  to view this content.

**nimrod** · 02-14-2011, 03:49 PM

Please Login or Register  to view this content.

**leaning** · 02-14-2011, 04:03 PM

Nimrod,

You are taking this to a whole 'nother level...

. Your code gets smaller and smaller each time.

a) The version I posted has the text of what each object is (Textbox, comment, etc.) which I like. (Yours just lists it as a "4" or "6".)

b) I couldn't get the 2nd version to work. 1004 Error on "objList=..." (This is with Excel 2003, so that may be why.)

MarvinP: What you are saying is what my question was about

. My macro is great in that it shows that the object exists. But it doesn't list where the object is. That is what I was trying to add. I know that ActiveCell.Address wasn't right, but I didn't know what to put in its place to list the specific cell where that object is.

Thanks all!

VR/Lost

**nimrod** · 02-14-2011, 05:15 PM

Hi Leaning:

The demo does , however , give you what you wanted ... it shows you how to get the objects position on the page. i.e. .BottomRightCell.Address . There is also another property call TopLeftCell.Address.

so in your code it would be ActiveSheet.Shapes(x).BottomRightCell.Address

Cheers

**nimrod** · 02-14-2011, 05:32 PM

This solution shows the name of the type instead of typeCode.

Please Login or Register  to view this content.

**snb** · 02-14-2011, 06:05 PM

Please Login or Register  to view this content.

**leaning** · 02-14-2011, 08:42 PM

All,

Nimrod: For the code on post #7, it is saying that Comments are Text Boxes. AFA everything else, you fixed my code in #6 and in addition provided more concise, alternate codes which did the same thing...that's good help!

snb: I am going to take a look at your code and compare its output to my Nimrod-enhanced code and Nimrod's other code. Thanks for that!

Good stuff!

VR/Lost

**leaning** · 02-14-2011, 09:01 PM

Nimrod,

Topleft and Bottomright aren't working.

I put both of our codes in a workbook and then put a comment and a txtbox on a sheet. When the codes run, the macro addresses don't match the actual sheet addresses.

See attached.

(Hmmmm..)

VR/Lost