Write Named Range Name in Cell via VBA

[bradleyc]

I have a report I'm building that contains a log sheet whenever a user changes a value on the first page. This page has three named ranges in which users can change or input values. I would like to have the name of the range they edited included in the log sheet message. What I have so far works fine with the exception that it doesn't display the named range name. Is there a way to do this?

Thanks!

Please Login or Register  to view this content.

[LJMetzger]

Hi bradleyc and welcome to ExcelForum,

Thank you for using CODE TAGS around your code.

See the attached sample file which incorporates the following function into your code:

Please Login or Register  to view this content.

Lewis

[protonLeah]

Also consider:

Please Login or Register  to view this content.

[bradleyc]

Thanks for the responses!
LJ, I tried out your code, and while there is a little extra info (like the sheet names being displayed), it seems to be exactly what I was looking for!

Leah, I also tried out yours, and I had some questions. When you ReDim the NameInfo array from 1 to Ndx, why do you also have 1 to 2? Ndx should hold 3 (the number of names in my sheet) right? And I was your comment about the name and address being two columns to the right, but where? I looked and didn't see the name anywhere.

Again, thanks for your responses!

[protonLeah]

I used: ReDim NameInfo(1 To Ndx, 1 To 2)
1 to Ndx is the number of rows corresponding to the number of names found,
1 to 2 for a 2 column array;
range names in column 1 and the range adddress in column 2 (in case it was needed elsewhere).

.Offset(0, 2).Resize(columnsize:=2).Value = Array(NameInfo(Ndx, 1), NameInfo(Ndx, 2))

is inside your "With Target" block so it puts the name & address two columns over from the target