Related: tdf#137577 Be able to select a global named range from Name Box

... if an identical sheet-local name exists.

Change-Id: I6d92a7ed93e81da64f60c26fd81eb6775582b053
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/113384
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins

4 files changed, 63 insertions, 25 deletions

diff --git a/sc/inc/rangeutl.hxx b/sc/inc/rangeutl.hxx
index aca68c6c3def..2f4a988725be 100644
--- a/sc/inc/rangeutl.hxx
+++ b/sc/inc/rangeutl.hxx
@@ -32,7 +32,14 @@ class ScArea;
 class ScDocument;
 class ScRangeList;
 
-enum RutlNameScope { RUTL_NONE=0, RUTL_NAMES, RUTL_DBASE };
+enum RutlNameScope
+{
+    RUTL_NONE = 0,
+    RUTL_NAMES,
+    RUTL_NAMES_LOCAL,
+    RUTL_NAMES_GLOBAL,
+    RUTL_DBASE
+};
 
 class SC_DLLPUBLIC ScRangeUtil
 {
diff --git a/sc/source/core/tool/rangeutl.cxx b/sc/source/core/tool/rangeutl.cxx
index 18473e0a3d5f..a80db03c56f1 100644
--- a/sc/source/core/tool/rangeutl.cxx
+++ b/sc/source/core/tool/rangeutl.cxx
@@ -248,35 +248,43 @@ bool ScRangeUtil::MakeRangeFromName (
     SCROW nRowStart = 0;
     SCROW nRowEnd = 0;
 
-    if( eScope==RUTL_NAMES )
+    if (eScope == RUTL_NAMES || eScope == RUTL_NAMES_LOCAL || eScope == RUTL_NAMES_GLOBAL)
     {
         OUString aName(rName);
         SCTAB nTable = nCurTab;
 
-        // First handle UI names like "local1 (Sheet1)", which point to a local
-        // range name.
-        const sal_Int32 nEndPos = aName.getLength() - 1;
-        if (rName[nEndPos] == ')')
+        if (eScope != RUTL_NAMES_GLOBAL)
         {
-            const sal_Int32 nStartPos = aName.indexOf(" (");
-            if (nStartPos != -1)
+            // First handle UI names like "local1 (Sheet1)", which point to a
+            // local range name.
+            const sal_Int32 nEndPos = aName.getLength() - 1;
+            if (rName[nEndPos] == ')')
             {
-                OUString aSheetName = aName.copy(nStartPos+2, nEndPos-nStartPos-2);
-                if (rDoc.GetTable(aSheetName, nTable))
+                const sal_Int32 nStartPos = aName.indexOf(" (");
+                if (nStartPos != -1)
                 {
-                    aName = aName.copy(0, nStartPos);
+                    OUString aSheetName = aName.copy(nStartPos+2, nEndPos-nStartPos-2);
+                    if (rDoc.GetTable(aSheetName, nTable))
+                    {
+                        aName = aName.copy(0, nStartPos);
+                        eScope = RUTL_NAMES_LOCAL;
+                    }
+                    else
+                        nTable = nCurTab;
                 }
-                else
-                    nTable = nCurTab;
             }
         }
-        // Then check for local range names.
-        ScRangeName* pRangeNames = rDoc.GetRangeName( nTable );
-        ScRangeData* pData = nullptr;
+
         aName = ScGlobal::getCharClassPtr()->uppercase(aName);
-        if ( pRangeNames )
-            pData = pRangeNames->findByUpperName(aName);
-        if (!pData)
+        ScRangeData* pData = nullptr;
+        if (eScope != RUTL_NAMES_GLOBAL)
+        {
+            // Check for local range names.
+            ScRangeName* pRangeNames = rDoc.GetRangeName( nTable );
+            if ( pRangeNames )
+                pData = pRangeNames->findByUpperName(aName);
+        }
+        if (!pData && eScope != RUTL_NAMES_LOCAL)
             pData = rDoc.GetRangeName()->findByUpperName(aName);
         if (pData)
         {
diff --git a/sc/source/ui/app/inputwin.cxx b/sc/source/ui/app/inputwin.cxx
index 3473ffc48109..2b5a48c09d38 100644
--- a/sc/source/ui/app/inputwin.cxx
+++ b/sc/source/ui/app/inputwin.cxx
@@ -101,7 +101,8 @@ enum ScNameInputType
 {
     SC_NAME_INPUT_CELL,
     SC_NAME_INPUT_RANGE,
-    SC_NAME_INPUT_NAMEDRANGE,
+    SC_NAME_INPUT_NAMEDRANGE_LOCAL,
+    SC_NAME_INPUT_NAMEDRANGE_GLOBAL,
     SC_NAME_INPUT_DATABASE,
     SC_NAME_INPUT_ROW,
     SC_NAME_INPUT_SHEET,
@@ -2323,14 +2324,23 @@ static ScNameInputType lcl_GetInputType( const OUString& rText )
         SCTAB nNameTab;
         sal_Int32 nNumeric;
 
+        // From the context we know that when testing for a range name
+        // sheet-local scope names have " (sheetname)" appended and global
+        // names don't and can't contain ')', so we can force one or the other.
+        const RutlNameScope eNameScope =
+            ((!rText.isEmpty() && rText[rText.getLength()-1] == ')') ? RUTL_NAMES_LOCAL : RUTL_NAMES_GLOBAL);
+
         if (rText == ScResId(STR_MANAGE_NAMES))
             eRet = SC_MANAGE_NAMES;
         else if ( aRange.Parse( rText, rDoc, eConv ) & ScRefFlags::VALID )
             eRet = SC_NAME_INPUT_RANGE;
         else if ( aAddress.Parse( rText, rDoc, eConv ) & ScRefFlags::VALID )
             eRet = SC_NAME_INPUT_CELL;
-        else if ( ScRangeUtil::MakeRangeFromName( rText, rDoc, nTab, aRange, RUTL_NAMES, eConv ) )
-            eRet = SC_NAME_INPUT_NAMEDRANGE;
+        else if ( ScRangeUtil::MakeRangeFromName( rText, rDoc, nTab, aRange, eNameScope, eConv ) )
+        {
+            eRet = ((eNameScope == RUTL_NAMES_LOCAL) ? SC_NAME_INPUT_NAMEDRANGE_LOCAL :
+                    SC_NAME_INPUT_NAMEDRANGE_GLOBAL);
+        }
         else if ( ScRangeUtil::MakeRangeFromName( rText, rDoc, nTab, aRange, RUTL_DBASE, eConv ) )
             eRet = SC_NAME_INPUT_DATABASE;
         else if ( comphelper::string::isdigitAsciiString( rText ) &&
@@ -2375,7 +2385,8 @@ IMPL_LINK_NOARG(ScPosWnd, ModifyHdl, weld::ComboBox&, void)
             pStrId = STR_NAME_INPUT_CELL;
             break;
         case SC_NAME_INPUT_RANGE:
-        case SC_NAME_INPUT_NAMEDRANGE:
+        case SC_NAME_INPUT_NAMEDRANGE_LOCAL:
+        case SC_NAME_INPUT_NAMEDRANGE_GLOBAL:
             pStrId = STR_NAME_INPUT_RANGE;      // named range or range reference
             break;
         case SC_NAME_INPUT_DATABASE:
@@ -2483,6 +2494,7 @@ void ScPosWnd::DoEnter()
                 }
                 else
                 {
+                    bool bForceGlobalName = false;
                     // for all selection types, execute the SID_CURRENTCELL slot.
                     if (eType == SC_NAME_INPUT_CELL || eType == SC_NAME_INPUT_RANGE)
                     {
@@ -2492,13 +2504,19 @@ void ScPosWnd::DoEnter()
                         aRange.ParseAny(aText, rDoc, rDoc.GetAddressConvention());
                         aText = aRange.Format(rDoc, ScRefFlags::RANGE_ABS_3D, ::formula::FormulaGrammar::CONV_OOO);
                     }
+                    else if (eType == SC_NAME_INPUT_NAMEDRANGE_GLOBAL)
+                    {
+                        bForceGlobalName = true;
+                    }
 
                     SfxStringItem aPosItem( SID_CURRENTCELL, aText );
                     SfxBoolItem aUnmarkItem( FN_PARAM_1, true );        // remove existing selection
+                    // FN_PARAM_2 reserved for AlignToCursor
+                    SfxBoolItem aForceGlobalName( FN_PARAM_3, bForceGlobalName );
 
                     pViewSh->GetViewData().GetDispatcher().ExecuteList( SID_CURRENTCELL,
                                         SfxCallMode::SYNCHRON | SfxCallMode::RECORD,
-                                        { &aPosItem, &aUnmarkItem });
+                                        { &aPosItem, &aUnmarkItem, &aForceGlobalName });
                 }
             }
         }
diff --git a/sc/source/ui/view/tabvwsh3.cxx b/sc/source/ui/view/tabvwsh3.cxx
index e8fb0d8a413d..7bbbd9647a4e 100644
--- a/sc/source/ui/view/tabvwsh3.cxx
+++ b/sc/source/ui/view/tabvwsh3.cxx
@@ -313,6 +313,10 @@ void ScTabViewShell::Execute( SfxRequest& rReq )
                 if (pReqArgs->GetItemState(FN_PARAM_2, true, &pItem) == SfxItemState::SET)
                     bAlignToCursor = static_cast<const SfxBoolItem*>(pItem)->GetValue();
 
+                bool bForceGlobalName = false;
+                if (pReqArgs->GetItemState(FN_PARAM_3, true, &pItem) == SfxItemState::SET)
+                    bForceGlobalName = static_cast<const SfxBoolItem*>(pItem)->GetValue();
+
                 if ( nSlot == SID_JUMPTOMARK )
                 {
                     //  URL has to be decoded for escaped characters (%20)
@@ -369,8 +373,9 @@ void ScTabViewShell::Execute( SfxRequest& rReq )
                 // Is it a named area (first named ranges then database ranges)?
                 else
                 {
+                    const RutlNameScope eScope = (bForceGlobalName ? RUTL_NAMES_GLOBAL : RUTL_NAMES);
                     formula::FormulaGrammar::AddressConvention eConv = rDoc.GetAddressConvention();
-                    if( ScRangeUtil::MakeRangeFromName( aAddress, rDoc, nTab, aScRange, RUTL_NAMES, eConv ) ||
+                    if( ScRangeUtil::MakeRangeFromName( aAddress, rDoc, nTab, aScRange, eScope, eConv ) ||
                         ScRangeUtil::MakeRangeFromName( aAddress, rDoc, nTab, aScRange, RUTL_DBASE, eConv ) )
                     {
                         nResult |= ScRefFlags::VALID;