Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    System Names Conflict (2000 sp-3/ 98SE)

    I'm using named ranges in code, but I see the potential for conflict with system-generated names, created during program operation. For instance, my code was working fine on a named range
    MyRange='Sheet Name'!$A$3:$AA$178 .
    It then broke down for the same target cells, because a new name
    'Sheet Name'!_FilterDatabase='Sheet Name'!$A$1:$AA$178
    had claimed "ownership" of those same cells. Specifically, the Cells context menu creation became messed up.

    I can think of a few ways of dealing with this, such as applying the "reverse" of post 478,327 and deleting system names, but I don't know if this is advisable. Looking for any general thoughts on dealing with such conflicts.

    thanks
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    I'm not sure I understand. Why would code break down if there are two names referring to the same range of cells?

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    Alan,

    I do not think that there should be any real conflict, as assigning a new name to a range does not actually delete the original name that range may have had. In short any given range may yhave more than one name.

    Or have I misunderstood the dilemma ?

    Andrew C

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    OK, more details. I build the cells context menu in a way that is dependent on the named range that the user clicks, as follows:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black>
    <font color=blue>Private</font color=blue> <font color=blue>Sub</font color=blue> Worksheet_BeforeRightClick(<font color=blue>ByVal</font color=blue> Target <font color=blue>As</font color=blue> Range, Cancel <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>)
    <font color=blue>Dim</font color=blue> nme As Name, strActive As <font color=blue>String</font color=blue>
    strActive = ActiveSheet.Name

    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> nme <font color=blue>In</font color=blue> ThisWorkbook.Names
    <font color=blue>If</font color=blue> SheetName(nme.Name) = strActive <font color=blue>Then</font color=blue>
    <font color=blue>If</font color=blue> Union(Target.Cells(1, 1), Range(nme.Name)).Address = _
    Range(nme.Name).Address <font color=blue>Then</font color=blue>
    <font color=448800>'Here pass nme.name to sub that builds the commandbar</font color=448800>
    AddNameCommandbar nme.Name
    <font color=448800>'Dismiss the default right-click menu</font color=448800>
    Cancel = <font color=blue>True</font color=blue>
    <font color=blue>Exit</font color=blue> <font color=blue>For</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Next</font color=blue> nme

    End <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Since I'm enumerating all the names in the workbook, the first on encountered that satisfies the IF statement will have its .Name value passed to the AddNameCommandbar procedure, which then identifies it within the available cases of a Select Case construct. If it's not recognized, it ends up with the Case Else default, which is not the desired result of course.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    > In short any given range may yhave more than one name.

    I know... and that's the problem <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. See my reply to Hans for deeper dilemma details.

    Alan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    So what would be the desired result if a range has two or more names? Should the right-click menu have items for each name, or...?

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    I think I see your point in the general case overlapping named ranges, and this would no doubt be complex. In my case, however, this does not happen by design; instead it happens as a result of system-generated overlaps. In the case I cited first, I suppose
    'Sheet Name'!_FilterDatabase='Sheet Name'!$A$1:$AA$178
    must come before
    MyRange='Sheet Name'!$A$3:$AA$178
    in the Names collection and is inappropriately selected as the name.

    Alan

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    You could use this function to determine whether a name is a system name:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Function</font color=blue> IsSysName(sName <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>) <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>
    <font color=blue>If</font color=blue> sName <font color=blue>Like</font color=blue> "*_FilterDatabase" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>Exit</font color=blue> <font color=blue>Function</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>If</font color=blue> sName <font color=blue>Like</font color=blue> "*Print_Area" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>Exit</font color=blue> <font color=blue>Function</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>If</font color=blue> sName <font color=blue>Like</font color=blue> "*Print_Titles" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>If</font color=blue> sName <font color=blue>Like</font color=blue> "*.wvu.*" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> If
    If sName <font color=blue>Like</font color=blue> "*wrn.*" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> If
    If sName <font color=blue>Like</font color=blue> "*!Criteria" <font color=blue>Then</font color=blue>
    IsSysName = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> If
    End <font color=blue>Function</font color=blue>
    <font color=blue>Sub</font color=blue> SortNames()</font color=black></code></div hiblock>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    You could use the method from <post#= 478327>post 478327</post#> in an If statement to exclude system-generated names:

    For Each nme In ThisWorkbook.Names
    ' Exclude system names
    If (InStr(nme.Name, "_FilterDatabase") + _
    InStr(nme.Name, "Print_Area") + _
    InStr(nme.Name,"Print_Titles") + _
    InStr(nme.Name,".wvu.") + _
    InStr(nme.Name,".wrn.") + _
    InStr(nme.Name,"!Criteria")) = 0 Then
    ' If name refers to active sheet
    If SheetName(nme.Name) = strActive Then

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    Yes, thanks Jan. I was thinking along similar lines there and considering ignoring any name with a "!". Would this be common to all built-in system names, or just the one I stumbled on? I guess the initial post I cited from Steve may give me the "ignore list" I'm after:
    <pre> If (InStr(Nm.Name,"_FilterDatabase") + _
    InStr(Nm.Name,"Print_Area") + _
    InStr(Nm.Name,"Print_Titles") + _
    InStr(Nm.Name,".wvu.") + _
    InStr(Nm.Name,".wrn.") + _
    InStr(Nm.Name,"!Criteria")) = 0 then 'Ignore Nm
    </pre>

    Alan

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    Hans, that was only 13 seconds! I must be getting faster in my old age. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    cheers
    Alan

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    Seconds? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    Your post:
    HansV
    (Administrator)
    26-Jul-05 00:37

    My near identical post:
    AlanMiller
    (WMVP)
    26-Jul-05 00:50

    Alan

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    My XL2K tells me that is 13 MINUTES. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Names Conflict (2000 sp-3/ 98SE)

    I do declare! I can't believe I misread that all too familiar date/ time so badly <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.
    Time to shutdown both Windows and me. A good night to all.

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •