Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named range - BUG
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named ran
Normally, Excel doesn't allow you to create named ranges that could be
confused with a cell address. -- Regards, Tom Ogilvy " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named ran
Ok, that's fair, but this is definitely a bug, right?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named ran
BTW: The loop through the Names collection works!
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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 -- Regards, Tom Ogilvy " wrote: Ok, that's fair, but this is definitely a bug, right? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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. :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
I almost fogot! How would you improve the code's accuracy without
checking other sources like the names collection? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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 wrote: I almost fogot! How would you improve the code's accuracy without checking other sources like the names collection? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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! :D I'll be activating the sheet instead of looping the Names collection. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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 -- Regards, Tom Ogilvy "Troy" wrote in message ps.com... 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 wrote: I almost fogot! How would you improve the code's accuracy without checking other sources like the names collection? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shape ControlFormat.LinkedCell returns wrong Address for named
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET function in named range returning wrong # of rows | Excel Worksheet Functions | |||
HLOOKUP using a named range returns #VALUE! | Excel Worksheet Functions | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
Address of named range | Excel Worksheet Functions | |||
Named range returns 1004 error | Excel Programming |