Shape ControlFormat.LinkedCell returns wrong Address for named range - BUG

[bowlegtroy]

Here is an example that I need confirmed as a bug:

1. Create a new workbook

2. On Worksheet 3, insert a named range named "Test" in Cell A1

3. On Worksheet 2, insert a form check box (forms toolbar) and
reference "Test" as the linked cell. In the Alternative Text, enter
this: "CB_Sheet2_ReferencesSheet3_NamedRange_test"

4. On Worksheet 2, insert a form check box (forms toolbar) and
reference "C1" as the linked cell. In the Alternative Text, enter this:
"CB_Sheet2_ReferencesSelf_C1"

5. On Worksheet 1, insert a form check box (forms toolbar) and
reference "A1" as the linked cell. In the Alternative Text, enter this:
"CB_Sheet1_ReferencesSelf_A1"

NOTE: Don't use the selector tool to reference the cell, just type it
in manually.

Press Alt+F11, and insert the following Module code (not in a form's
module or the thiswoorkbook module):

Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1
For Each wks1 In ThisWorkbook.Worksheets
For Each sh In wks1.Shapes
Set rngShapeRef = Range(sh.ControlFormat.LinkedCell)
Debug.Print intCounter & ": " & sh.AlternativeText & ": " &
rngShapeRef.Address(External:=True)
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next

End Function

Run that code and look at the results:

ActiveSheet: Sheet1
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet1!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet1!$C$1

ActiveSheet: Sheet2
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet2!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet2!$C$1

ActiveSheet: Sheet3
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet3!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet3!$C$1

Either 1 or 3 will be incorrect based on the active sheet, and if the
active sheet houses neither one, then both are wrong!!!

So, the reason I stumbled on this problem is that I've been asked to
document a workbook with many worksheets, thousands of named
references, and hundreds of controls. NOTE: These are not necessarily
workbook that I have control over for changes to be made. It is
someone elses hard work and I'm to document it, but not make changes to
it.

I'm attempting to write code to document all cell references (yes, I
have the cell reference code down), but this object/shape reference is
really messed up!

I think I have a workaround:
Loop the names collection to see if the ControlFormat.LinkedCell is a
named range or not. If it is, then use the code, otherwise use the
following line instead:

Debug.Print intCounter & ": " & sh.AlternativeText & ": [" &
ThisWorkbook.Name & "]" & wks1.Name & "!" & rngShapeRef.Address

Someone please confirm I'm not seeing things or if there is an
alternative to looping the names collection for every object (it would
be a loop over 180 times) and string comparison. It adds a lot of time
to the documentation procedure that checks for the true address of the
linked cell.

Also, this workaround is still not foolproof: What if the spreadsheet
I'm documenting has a named range named "A1" or "C1" (yes someone
*might* actually do this). Which is the control referencing? I've not
tested that yet, but this is a big nasty bug for my documentation
purposes.

Does anyone have a better way to get the *correct* cell reference every
time?

Please NO PM as I am using my "spam" mailbox to post this with. Please
reply here.

 

[Tom Ogilvy]

Normally, Excel doesn't allow you to create named ranges that could be
confused with a cell address.

--
Regards,
Tom Ogilvy

Here is an example that I need confirmed as a bug:

1. Create a new workbook

2. On Worksheet 3, insert a named range named "Test" in Cell A1

3. On Worksheet 2, insert a form check box (forms toolbar) and
reference "Test" as the linked cell. In the Alternative Text, enter
this: "CB_Sheet2_ReferencesSheet3_NamedRange_test"

4. On Worksheet 2, insert a form check box (forms toolbar) and
reference "C1" as the linked cell. In the Alternative Text, enter this:
"CB_Sheet2_ReferencesSelf_C1"

5. On Worksheet 1, insert a form check box (forms toolbar) and
reference "A1" as the linked cell. In the Alternative Text, enter this:
"CB_Sheet1_ReferencesSelf_A1"

NOTE: Don't use the selector tool to reference the cell, just type it
in manually.

Press Alt+F11, and insert the following Module code (not in a form's
module or the thiswoorkbook module):

Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1
For Each wks1 In ThisWorkbook.Worksheets
For Each sh In wks1.Shapes
Set rngShapeRef = Range(sh.ControlFormat.LinkedCell)
Debug.Print intCounter & ": " & sh.AlternativeText & ": " &
rngShapeRef.Address(External:=True)
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next

End Function

Run that code and look at the results:

ActiveSheet: Sheet1
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet1!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet1!$C$1

ActiveSheet: Sheet2
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet2!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet2!$C$1

ActiveSheet: Sheet3
1: CB_Sheet1_ReferencesSelf_A1: [Book1]Sheet3!$A$1
2: CB_Sheet2_ReferencesSheet3_NamedRange_test: [Book1]Sheet3!$A$1
3: CB_Sheet2_ReferencesSelf_C1: [Book1]Sheet3!$C$1

Either 1 or 3 will be incorrect based on the active sheet, and if the
active sheet houses neither one, then both are wrong!!!

So, the reason I stumbled on this problem is that I've been asked to
document a workbook with many worksheets, thousands of named
references, and hundreds of controls. NOTE: These are not necessarily
workbook that I have control over for changes to be made. It is
someone elses hard work and I'm to document it, but not make changes to
it.

I'm attempting to write code to document all cell references (yes, I
have the cell reference code down), but this object/shape reference is
really messed up!

I think I have a workaround:
Loop the names collection to see if the ControlFormat.LinkedCell is a
named range or not. If it is, then use the code, otherwise use the
following line instead:

Debug.Print intCounter & ": " & sh.AlternativeText & ": [" &
ThisWorkbook.Name & "]" & wks1.Name & "!" & rngShapeRef.Address

Someone please confirm I'm not seeing things or if there is an
alternative to looping the names collection for every object (it would
be a loop over 180 times) and string comparison. It adds a lot of time
to the documentation procedure that checks for the true address of the
linked cell.

Also, this workaround is still not foolproof: What if the spreadsheet
I'm documenting has a named range named "A1" or "C1" (yes someone
*might* actually do this). Which is the control referencing? I've not
tested that yet, but this is a big nasty bug for my documentation
purposes.

Does anyone have a better way to get the *correct* cell reference every
time?

Please NO PM as I am using my "spam" mailbox to post this with. Please
reply here.

 

[bowlegtroy]

Ok, that's fair, but this is definitely a bug, right?

 

[Troy]

BTW: The loop through the Names collection works!

 

[Tom Ogilvy]

Is it a bug - yes, but not what you think.

The bug is really in your code, not in excel's behavior. The checkbox
remains linked to Sheet2!C1. You could say it is a bug that LinkedCell
returns a string that is exactly what you entered rather than a range
reference, but It don't see it as such. If you coded the rule as an
unqualified cell reference is to the sheet containing the control and a
qualified reference is just that, then you will get the correct
interpretation.

You haven't done that in your code. You just use the cell address C1 and
pass it to an unqualified range argument

Range("C1") will always refer to the activesheet, but that isn't the cell
linked to by the control

to test, make sheet1 active. Your code would report it is linked to
sheet1!C1, but if you do

worksheets("sheet2").Checkboxes(2).Value = True

then it is Sheet2!C1 that gets changed even with sheet1 or sheet3 active.
So your code isn't reporting properly.

So yes, I will agree your code definitely has a bug. <g>

 

[Troy]

Interesting approach to the problem. However, using the logic you
propose, then selecting Sheet1, and programmatically checking Sheet2,
Check Box 2, should make Sheet1's cell C1 equal TRUE. I mean, by the
logic you presented, the reference in the check box is unqualified, so
it should point to the unqualified cell reference on the activesheet
when I check it?

It doesn't.

Worksheets("Sheet1").Activate
Worksheets("Sheet2").Shapes("Check Box 2").ControlFormat.Value = True
Debug.Print "1: " & ActiveSheet.Name
Debug.Print "2: " & ActiveSheet.Range("C1").Value
Debug.Print "3: " & Worksheets("Sheet2").Range("C1").Value

Results:
1: Sheet1
2:
3: True

What I'm saying is that internally, Excel knows that the unqualified
reference is to the parent of the check box vs. a qualified reference.

So, when I ask Excel for what it thinks the fully qualifed reference
is, it should give me the one that it believes is the correct
reference, and not what the "active sheet + unqualifed reference"
concatenated together is.

While I agree that the shape itself doesn't list a qualified reference
(and the person could have coded it better), Excel does something
internally to qualify an "unqualified reference" to the parent sheet of
the shape.

I don't think it's a bug in the code to ask for a correct reference of
a shape, consistently, without checking other sources for information
regardless if the path is qualified or not. So when I ask for
something relatively simple like the Address(External:=True) for the
range, I expect Excel to give that to me correctly.

I should *never* have to go to the Names collection to determine if the
referenced location is in that collection or not. Why not? Becuase
now, in order to get accurate results, I must do that for all
references whether they are qualified or not. That's just silly.

Don't get me wrong, I respect your opinion, have seen you post on
thousands of threads, and I'm not trying to insult you, but I don't
think the logic you proposed is all that logical.

 

[Troy]

I almost fogot! How would you improve the code's accuracy without
checking other sources like the names collection?

 

[Troy]

Hmmmm....I think I answered my own question on this last one:

Activate the sheet I'm checking the shapes collection for. That way,
any unqualified reference will return the appropriate sheet (the
currently activated one, which happens to be the same sheet the control
is located on).

Although it still sucks that I have to activate each sheet as I go
along, it's at least shorter than looping the names collection!

 

[Troy]

Scratch this last one. I was feeling argumentative.

I think I see the light - it always uses the parent sheet and not the
active sheet (unless the range was created with an unqualifed
reference, in which case the activesheet at the time of creation *is*
the parent!).

So when I give a range an unqualifed reference, it refers to the active
sheet simply because that could be considered the parent at the time
the range was created. I guess this makes sense.

Take my original code and add these lines in the second loop to see why
I've changed my tune:

Dim x as Range
Set x = Range("$AB$1")
Debug.Print x.Address(External:=True)

Light bulb = ON! Still dim, but on!

I'll be activating the sheet instead of looping the Names collection.

 

[Tom Ogilvy]

Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape
Dim s as String

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1
For Each wks1 In ThisWorkbook.Worksheets
For Each sh In wks1.Shapes
s = sh.ControlFormat.LinkedCell
if instr(1,s,"!",vbTextCompare) then
Set rngShapeRef = Range(s)
else
set rngShapeRef = sh.Range(s)
end if
Debug.Print intCounter & ": " & sh.AlternativeText & _
": " & rngShapeRef.Address(External:=True)
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next

End Function

 

[Troy]

Tom,

I couldn't get that code to work, but I could get a concatenated string
to work instead.

There is also a small addition to the code below - if there is a space
in the sheet name, then you must account for it and concatenate with
single quotes. Additional to that, if you have a single quote
(apostrophe) in the name already ("Troy's Sheet 1"), there is an even
bigger string parse routine that I did not list!!!

I've found that using the second set of code below to activate the
sheet is the best method.

Here is your addition to the code, modified to get the shape parent and
concatenate with the shape's linkedcell property:

Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape
Dim s As String

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1
For Each wks1 In ThisWorkbook.Worksheets
For Each sh In wks1.Shapes
s = sh.ControlFormat.LinkedCell
If InStr(1, s, "!", vbTextCompare) Then
Set rngShapeRef = Range(s)
Else
If InStr(sh.Parent.Name, " ") > 0 Then
s = "'" & sh.Parent.Name & "'!" & s
Else
s = sh.Parent.Name & "!" & s
End If
Set rngShapeRef = Range(s)
End If
Debug.Print intCounter & ": " & sh.AlternativeText & _
": " & rngShapeRef.Address(External:=True)
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next
End Function


Since I'm not a big fan of string parsing, the caveates it presents, or
the additional time it takes for larger string operations, I prefer to
use the simplest method - Activate the sheet so that unqualified ranges
are created using the activesheet as the parent:

Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1

For Each wks1 In ThisWorkbook.Worksheets
' ACTIVATE THE CURRENT SHEET. Reason: Ranges created in code
' will use the activesheet as the parent of a range if the
'range isn't fully qualified.
'e.g.: Fully Qualified range: Sheet1!A1 OR 'Sheet 1'!A1 OR 'Sheet
1'!$A$1, etc
'e.g.: UnQualified range (but completely valid): A1 OR $A$1
wks1.Activate
For Each sh In wks1.Shapes
Set rngShapeRef = Range(sh.ControlFormat.LinkedCell)
Debug.Print intCounter & ": " & sh.AlternativeText & _
": " & rngShapeRef.Address(External:=True)
'OR if you don't want the Name of the workbook
'Debug.Print intCounter & ": " & sh.AlternativeText & _
' ": " & rngShapeRef.Parent.Name & "!" & rngShapeRef.Address
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next
End Function

Also, for anyone else reading this thread, and the code, obviously the
first For...Next is not necessary. I only wanted to do it that way for
my original post in order to show that the ActiveSheet was showing as
the parent when it really wasn't. However, after debating this point
with Tom here, it was determined that this is how unqualifed ranges in
code are created.

Thanks for pointing me in the right direction Tom!