tdf#157657 - sc: add option to make data validity case-sensitive

Add option to check data validity with case sensitive and case
insensitive. The default set would remain the original case insensitive
mode for data validity.

Change-Id: Ic4fe56e2b31b7fc348b742f3b95fb44cd35bc49d
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/167280
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

20 files changed, 148 insertions, 28 deletions

diff --git a/offapi/com/sun/star/sheet/TableValidation.idl b/offapi/com/sun/star/sheet/TableValidation.idl
index 0f0c7cccff66..2358c9a07e13 100644
--- a/offapi/com/sun/star/sheet/TableValidation.idl
+++ b/offapi/com/sun/star/sheet/TableValidation.idl
@@ -99,6 +99,13 @@ published service TableValidation
      */
     [optional,property] short ShowList;
 
+
+    /** specifies if validation is case sensitive.
+
+       @since LibreOffice 24.8
+     */
+    [optional, property] boolean IsCaseSensitive;
+
 };
 
 
diff --git a/oovbaapi/ooo/vba/excel/XValidation.idl b/oovbaapi/ooo/vba/excel/XValidation.idl
index 082f498ab7f5..ba84cbd7c8a9 100644
--- a/oovbaapi/ooo/vba/excel/XValidation.idl
+++ b/oovbaapi/ooo/vba/excel/XValidation.idl
@@ -38,6 +38,7 @@ interface XValidation
     [attribute, readonly] string Formula1;
     [attribute, readonly] string Formula2;
     [attribute, readonly] long Type;
+    [attribute] boolean CaseSensitive;
     void Delete();
     void Add( [in] any Type, [in] any AlertStyle, [in] any Operator, [in] any Formula1, [in] any Formula2);
 };
diff --git a/sc/inc/conditio.hxx b/sc/inc/conditio.hxx
index 430858122a75..24c42ffbe703 100644
--- a/sc/inc/conditio.hxx
+++ b/sc/inc/conditio.hxx
@@ -56,6 +56,7 @@ struct RefUpdateMoveTabContext;
 
 //  nOptions Flags
 #define SC_COND_NOBLANKS    1
+#define SC_COND_CASESENS    2
 
 enum class ScConditionMode
 {
@@ -369,8 +370,13 @@ public:
 
     ScConditionMode GetOperation() const        { return eOp; }
     void SetOperation(ScConditionMode eMode);
+
     bool            IsIgnoreBlank() const       { return ( nOptions & SC_COND_NOBLANKS ) == 0; }
     SC_DLLPUBLIC void SetIgnoreBlank(bool bSet);
+
+    bool            IsCaseSensitive() const     { return ( nOptions & SC_COND_CASESENS ) != 0; }
+    SC_DLLPUBLIC void SetCaseSensitive(bool bSet);
+
     const OUString& GetSrcString() const         { return aSrcString; }
     const ScAddress& GetSrcPos() const           { return aSrcPos; }
 
diff --git a/sc/inc/fmtuno.hxx b/sc/inc/fmtuno.hxx
index 3796e820c075..91e3b66db020 100644
--- a/sc/inc/fmtuno.hxx
+++ b/sc/inc/fmtuno.hxx
@@ -168,6 +168,7 @@ private:
     OUString            aPosString;     // formula position as text
     sal_uInt16          nValMode;       // enum ScValidationMode
     bool                bIgnoreBlank;
+    bool                bCaseSensitive;
     sal_Int16           nShowList;
     bool                bShowInput;
     OUString            aInputTitle;
diff --git a/sc/inc/sc.hrc b/sc/inc/sc.hrc
index 54d4c0b5a0e2..9ef248660d7e 100644
--- a/sc/inc/sc.hrc
+++ b/sc/inc/sc.hrc
@@ -576,15 +576,16 @@ static_assert(SID_PREVIEW_END < SID_KEYFUNC_START, "calc slots ids trampling inf
 #define FID_VALID_VALUE1        TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+62)
 #define FID_VALID_VALUE2        TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+63)
 #define FID_VALID_BLANK         TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+64)
-#define FID_VALID_SHOWHELP      TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+65)
-#define FID_VALID_HELPTITLE     TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+66)
-#define FID_VALID_HELPTEXT      TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+67)
-#define FID_VALID_SHOWERR       TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+68)
-#define FID_VALID_ERRSTYLE      TypedWhichId<SfxUInt16Item>(SID_NEW_SLOTS+69)
-#define FID_VALID_ERRTITLE      TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+70)
-#define FID_VALID_ERRTEXT       TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+71)
-
-#define SID_REFRESH_DBAREA      (SID_NEW_SLOTS+72)
+#define FID_VALID_CASESENS      TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+65)
+#define FID_VALID_SHOWHELP      TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+66)
+#define FID_VALID_HELPTITLE     TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+67)
+#define FID_VALID_HELPTEXT      TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+68)
+#define FID_VALID_SHOWERR       TypedWhichId<SfxBoolItem>(SID_NEW_SLOTS+69)
+#define FID_VALID_ERRSTYLE      TypedWhichId<SfxUInt16Item>(SID_NEW_SLOTS+70)
+#define FID_VALID_ERRTITLE      TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+71)
+#define FID_VALID_ERRTEXT       TypedWhichId<SfxStringItem>(SID_NEW_SLOTS+72)
+
+#define SID_REFRESH_DBAREA      (SID_NEW_SLOTS+73)
 
 #define FID_FOCUS_POSWND        (SID_NEW_SLOTS+74)
 
diff --git a/sc/source/core/data/conditio.cxx b/sc/source/core/data/conditio.cxx
index 4c4ad49c67e7..87c0834d69f0 100644
--- a/sc/source/core/data/conditio.cxx
+++ b/sc/source/core/data/conditio.cxx
@@ -434,6 +434,16 @@ void ScConditionEntry::SetIgnoreBlank(bool bSet)
         nOptions |= SC_COND_NOBLANKS;
 }
 
+void ScConditionEntry::SetCaseSensitive(bool bSet)
+{
+    // The bit SC_COND_CASESENS is set if validation compare is case sensitive
+    // (only of valid)
+    if (bSet)
+        nOptions |= SC_COND_CASESENS;
+    else
+        nOptions &= ~SC_COND_CASESENS;
+}
+
 /**
  * Delete formula cells, so we re-compile at the next IsValid
  */
@@ -1172,10 +1182,10 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
     switch ( eOp )
     {
         case ScConditionMode::Equal:
-            bValid = ScGlobal::GetTransliteration().isEqual(aUpVal1, rArg);
+            bValid = ScGlobal::GetTransliteration(IsCaseSensitive()).isEqual(aUpVal1, rArg);
         break;
         case ScConditionMode::NotEqual:
-            bValid = !ScGlobal::GetTransliteration().isEqual(aUpVal1, rArg);
+            bValid = !ScGlobal::GetTransliteration(IsCaseSensitive()).isEqual(aUpVal1, rArg);
         break;
         case ScConditionMode::TopPercent:
         case ScConditionMode::BottomPercent:
@@ -1185,7 +1195,7 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
         case ScConditionMode::BelowAverage:
             return false;
         case ScConditionMode::BeginsWith:
-            bValid = ScGlobal::GetTransliteration().isMatch(aUpVal1, rArg);
+            bValid = ScGlobal::GetTransliteration(IsCaseSensitive()).isMatch(aUpVal1, rArg);
         break;
         case ScConditionMode::EndsWith:
         {
@@ -1197,7 +1207,7 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
             {
                 nStart = nStart - nLen;
                 sal_Int32 nMatch1(0), nMatch2(0);
-                bValid = ScGlobal::GetTransliteration().equals(rArg, nStart, nLen, nMatch1,
+                bValid = ScGlobal::GetTransliteration(IsCaseSensitive()).equals(rArg, nStart, nLen, nMatch1,
                                                                aUpVal1, 0, nLen, nMatch2);
             }
         }
@@ -1205,8 +1215,8 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
         case ScConditionMode::ContainsText:
         case ScConditionMode::NotContainsText:
         {
-            const OUString aArgStr(ScGlobal::getCharClass().lowercase(rArg));
-            const OUString aValStr(ScGlobal::getCharClass().lowercase(aUpVal1));
+            const OUString aArgStr(!IsCaseSensitive() ? ScGlobal::getCharClass().lowercase(rArg) : rArg);
+            const OUString aValStr(!IsCaseSensitive() ? ScGlobal::getCharClass().lowercase(aUpVal1) : aUpVal1);
             bValid = aArgStr.indexOf(aValStr) != -1;
 
             if(eOp == ScConditionMode::NotContainsText)
@@ -1215,7 +1225,7 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
         break;
         default:
         {
-            sal_Int32 nCompare = ScGlobal::GetCollator().compareString(
+            sal_Int32 nCompare = ScGlobal::GetCollator(IsCaseSensitive()).compareString(
                 rArg, aUpVal1 );
             switch ( eOp )
             {
@@ -1234,7 +1244,7 @@ bool ScConditionEntry::IsValidStr( const OUString& rArg, const ScAddress& rPos )
                 case ScConditionMode::Between:
                 case ScConditionMode::NotBetween:
                 {
-                    const sal_Int32 nCompare2 = ScGlobal::GetCollator().compareString(rArg, aUpVal2);
+                    const sal_Int32 nCompare2 = ScGlobal::GetCollator(IsCaseSensitive()).compareString(rArg, aUpVal2);
                     //  Test for NOTBETWEEN:
                     bValid = (nCompare > 0 && nCompare2 > 0) || (nCompare < 0 && nCompare2 < 0);
                     if ( eOp == ScConditionMode::Between )
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index f627509096fe..7d2bc0be9e65 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -1036,6 +1036,8 @@ bool ScValidationData::IsEqualToTokenArray( ScRefCellValue& rCell, const ScAddre
 {
     // create a condition entry that tests on equality and set the passed token array
     ScConditionEntry aCondEntry( ScConditionMode::Equal, &rTokArr, nullptr, *GetDocument(), rPos );
+    aCondEntry.SetCaseSensitive(IsCaseSensitive());
+
     return aCondEntry.IsCellValid(rCell, rPos);
 }
 
diff --git a/sc/source/filter/xml/XMLStylesExportHelper.cxx b/sc/source/filter/xml/XMLStylesExportHelper.cxx
index 0708bfd6c25d..9ca2b865835d 100644
--- a/sc/source/filter/xml/XMLStylesExportHelper.cxx
+++ b/sc/source/filter/xml/XMLStylesExportHelper.cxx
@@ -48,13 +48,15 @@ ScMyValidation::ScMyValidation()
     nShowList(0),
     bShowErrorMessage(false),
     bShowInputMessage(false),
-    bIgnoreBlanks(false)
+    bIgnoreBlanks(false),
+    bCaseSensitive(false)
 {
 }
 
 bool ScMyValidation::IsEqual(const ScMyValidation& aVal) const
 {
     return aVal.bIgnoreBlanks == bIgnoreBlanks &&
+        aVal.bCaseSensitive == bCaseSensitive &&
         aVal.bShowInputMessage == bShowInputMessage &&
         aVal.bShowErrorMessage == bShowErrorMessage &&
         aVal.aBaseCell == aBaseCell &&
@@ -109,6 +111,7 @@ void ScMyValidationsContainer::AddValidation(const uno::Any& aTempAny,
     aValidation.bShowInputMessage = bShowInputMessage;
     aValidation.aValidationType = aValidationType;
     aValidation.bIgnoreBlanks = ::cppu::any2bool(xPropertySet->getPropertyValue(SC_UNONAME_IGNOREBL));
+    aValidation.bCaseSensitive = ::cppu::any2bool(xPropertySet->getPropertyValue(SC_UNONAME_ISCASE));
     xPropertySet->getPropertyValue(SC_UNONAME_SHOWLIST) >>= aValidation.nShowList;
     xPropertySet->getPropertyValue(SC_UNONAME_ERRALSTY) >>= aValidation.aAlertStyle;
     uno::Reference<sheet::XSheetCondition> xCondition(xPropertySet, uno::UNO_QUERY);
@@ -322,6 +325,10 @@ void ScMyValidationsContainer::WriteValidations(ScXMLExport& rExport)
                 rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_ALLOW_EMPTY_CELL, XML_TRUE);
             else
                 rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_ALLOW_EMPTY_CELL, XML_FALSE);
+            // Validation Case Sensitive
+            if (rValidation.bCaseSensitive)
+                rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_CASE_SENSITIVE, XML_TRUE);
+
             if (rValidation.aValidationType == sheet::ValidationType_LIST)
             {
                 switch (rValidation.nShowList)
diff --git a/sc/source/filter/xml/XMLStylesExportHelper.hxx b/sc/source/filter/xml/XMLStylesExportHelper.hxx
index 9a8b9a240a80..c404e8154004 100644
--- a/sc/source/filter/xml/XMLStylesExportHelper.hxx
+++ b/sc/source/filter/xml/XMLStylesExportHelper.hxx
@@ -51,6 +51,7 @@ struct ScMyValidation
     bool                        bShowErrorMessage;
     bool                        bShowInputMessage;
     bool                        bIgnoreBlanks;
+    bool                        bCaseSensitive;
 
                                 ScMyValidation();
 
diff --git a/sc/source/filter/xml/xmlcelli.cxx b/sc/source/filter/xml/xmlcelli.cxx
index 5f6f5a59258f..a122075f1c68 100644
--- a/sc/source/filter/xml/xmlcelli.cxx
+++ b/sc/source/filter/xml/xmlcelli.cxx
@@ -780,6 +780,7 @@ void ScXMLTableRowCellContext::SetContentValidation( const ScRange& rScRange )
     );
 
     aScValidationData.SetIgnoreBlank( aValidation.bIgnoreBlanks );
+    aScValidationData.SetCaseSensitive( aValidation.bCaseSensitive );
     aScValidationData.SetListType( aValidation.nShowList );
 
     // set strings for error / input even if disabled (and disable afterwards)
diff --git a/sc/source/filter/xml/xmlcvali.cxx b/sc/source/filter/xml/xmlcvali.cxx
index 20b1f235fd17..4946bf236264 100644
--- a/sc/source/filter/xml/xmlcvali.cxx
+++ b/sc/source/filter/xml/xmlcvali.cxx
@@ -46,6 +46,7 @@ class ScXMLContentValidationContext : public ScXMLImportContext
     OUString      sCondition;
     sal_Int16          nShowList;
     bool           bAllowEmptyCell;
+    bool           bIsCaseSensitive;
     bool           bDisplayHelp;
     bool           bDisplayError;
 
@@ -167,6 +168,7 @@ ScXMLContentValidationContext::ScXMLContentValidationContext( ScXMLImport& rImpo
     ScXMLImportContext( rImport ),
     nShowList(sheet::TableValidationVisibility::UNSORTED),
     bAllowEmptyCell(true),
+    bIsCaseSensitive(false),
     bDisplayHelp(false),
     bDisplayError(false)
 {
@@ -190,6 +192,10 @@ ScXMLContentValidationContext::ScXMLContentValidationContext( ScXMLImport& rImpo
             if (IsXMLToken(aIter, XML_FALSE))
                 bAllowEmptyCell = false;
             break;
+        case XML_ELEMENT( TABLE, XML_CASE_SENSITIVE ):
+            if (IsXMLToken(aIter, XML_TRUE))
+                bIsCaseSensitive = true;
+            break;
         case XML_ELEMENT( TABLE, XML_DISPLAY_LIST ):
             if (IsXMLToken(aIter, XML_NO))
             {
@@ -380,6 +386,7 @@ void SAL_CALL ScXMLContentValidationContext::endFastElement( sal_Int32 /*nElemen
     aValidation.bShowErrorMessage = bDisplayError;
     aValidation.bShowInputMessage = bDisplayHelp;
     aValidation.bIgnoreBlanks = bAllowEmptyCell;
+    aValidation.bCaseSensitive = bIsCaseSensitive;
     aValidation.nShowList = nShowList;
     GetScImport().AddValidation(aValidation);
 }
diff --git a/sc/source/filter/xml/xmlimprt.hxx b/sc/source/filter/xml/xmlimprt.hxx
index 843e86536088..925df7c6e785 100644
--- a/sc/source/filter/xml/xmlimprt.hxx
+++ b/sc/source/filter/xml/xmlimprt.hxx
@@ -106,6 +106,7 @@ struct ScMyImportValidation
     bool                                       bShowErrorMessage;
     bool                                       bShowInputMessage;
     bool                                       bIgnoreBlanks;
+    bool                                       bCaseSensitive;
 };
 
 typedef std::vector<ScMyImportValidation>           ScMyImportValidations;
diff --git a/sc/source/ui/dbgui/validate.cxx b/sc/source/ui/dbgui/validate.cxx
index e72951ce6027..99d9eba039da 100644
--- a/sc/source/ui/dbgui/validate.cxx
+++ b/sc/source/ui/dbgui/validate.cxx
@@ -364,6 +364,7 @@ ScTPValidationValue::ScTPValidationValue(weld::Container* pPage, weld::DialogCon
     , m_pRefEdit(nullptr)
     , m_xLbAllow(m_xBuilder->weld_combo_box("allow"))
     , m_xCbAllow(m_xBuilder->weld_check_button("allowempty"))
+    , m_xCbCaseSens(m_xBuilder->weld_check_button("casesens"))
     , m_xCbShow(m_xBuilder->weld_check_button("showlist"))
     , m_xCbSort(m_xBuilder->weld_check_button("sortascend"))
     , m_xFtValue(m_xBuilder->weld_label("valueft"))
@@ -452,6 +453,11 @@ void ScTPValidationValue::Reset( const SfxItemSet* rArgSet )
         bCheck = pItem->GetValue();
     m_xCbAllow->set_active( bCheck );
 
+    bool bCaseSensetive = false;
+    if (const SfxBoolItem* pItem = rArgSet->GetItemIfSet( FID_VALID_CASESENS ) )
+        bCaseSensetive = pItem->GetValue();
+    m_xCbCaseSens->set_active( bCaseSensetive );
+
     sal_Int32 nListType = ValidListType::UNSORTED;
     if( const SfxInt16Item* pItem = rArgSet->GetItemIfSet( FID_VALID_LISTTYPE ) )
         nListType = pItem->GetValue();
@@ -490,6 +496,7 @@ bool ScTPValidationValue::FillItemSet( SfxItemSet* rArgSet )
     rArgSet->Put( SfxStringItem( FID_VALID_VALUE1, GetFirstFormula() ) );
     rArgSet->Put( SfxStringItem( FID_VALID_VALUE2, GetSecondFormula() ) );
     rArgSet->Put( SfxBoolItem( FID_VALID_BLANK, m_xCbAllow->get_active() ) );
+    rArgSet->Put( SfxBoolItem( FID_VALID_CASESENS, m_xCbCaseSens->get_active() ) );
     rArgSet->Put( SfxInt16Item( FID_VALID_LISTTYPE, nListType ) );
     return true;
 }
@@ -644,6 +651,8 @@ IMPL_LINK_NOARG(ScTPValidationValue, SelectHdl, weld::ComboBox&, void)
     bool bCustom = (nLbPos == SC_VALIDDLG_ALLOW_CUSTOM);
 
     m_xCbAllow->set_sensitive( bEnable );   // Empty cell
+    m_xCbCaseSens->set_sensitive( bEnable &&
+        (bRange || bList || bCustom) ); // Case Sensitive
     m_xFtValue->set_sensitive( bEnable );
     m_xLbValue->set_sensitive( bEnable );
     m_xFtMin->set_sensitive( bEnable );
@@ -681,6 +690,7 @@ IMPL_LINK_NOARG(ScTPValidationValue, SelectHdl, weld::ComboBox&, void)
         }
     }
 
+    m_xCbCaseSens->set_visible( bRange || bList || bCustom ); // Case Sensitive
     m_xCbShow->set_visible( bRange || bList );
     m_xCbSort->set_visible( bRange || bList );
     m_xFtValue->set_visible( !bRange && !bList && !bCustom);
diff --git a/sc/source/ui/inc/validate.hxx b/sc/source/ui/inc/validate.hxx
index ee97afe13867..b6a642ba3758 100644
--- a/sc/source/ui/inc/validate.hxx
+++ b/sc/source/ui/inc/validate.hxx
@@ -115,6 +115,7 @@ private:
 
     std::unique_ptr<weld::ComboBox> m_xLbAllow;
     std::unique_ptr<weld::CheckButton> m_xCbAllow;      /// Allow blank cells.
+    std::unique_ptr<weld::CheckButton> m_xCbCaseSens;   /// Allow Case sensitive.
     std::unique_ptr<weld::CheckButton> m_xCbShow;       /// Show selection list in cell.
     std::unique_ptr<weld::CheckButton> m_xCbSort;       /// Sort selection list in cell.
     std::unique_ptr<weld::Label> m_xFtValue;
diff --git a/sc/source/ui/unoobj/fmtuno.cxx b/sc/source/ui/unoobj/fmtuno.cxx
index 1b9185591739..2fb8624571a5 100644
--- a/sc/source/ui/unoobj/fmtuno.cxx
+++ b/sc/source/ui/unoobj/fmtuno.cxx
@@ -52,6 +52,7 @@ static std::span<const SfxItemPropertyMapEntry> lcl_GetValidatePropertyMap()
         { SC_UNONAME_ERRMESS,  0,  cppu::UnoType<OUString>::get(),                0, 0},
         { SC_UNONAME_ERRTITLE, 0,  cppu::UnoType<OUString>::get(),                0, 0},
         { SC_UNONAME_IGNOREBL, 0,  cppu::UnoType<bool>::get(),                          0, 0},
+        { SC_UNONAME_ISCASE,   0,  cppu::UnoType<bool>::get(),                          0, 0},
         { SC_UNONAME_INPMESS,  0,  cppu::UnoType<OUString>::get(),                0, 0},
         { SC_UNONAME_INPTITLE, 0,  cppu::UnoType<OUString>::get(),                0, 0},
         { SC_UNONAME_SHOWERR,  0,  cppu::UnoType<bool>::get(),                          0, 0},
@@ -559,6 +560,7 @@ ScTableValidationObj::ScTableValidationObj(const ScDocument& rDoc, sal_uInt32 nK
             meGrammar1 = meGrammar2 = eGrammar;
             nValMode = sal::static_int_cast<sal_uInt16>( pData->GetDataMode() );
             bIgnoreBlank = pData->IsIgnoreBlank();
+            bCaseSensitive = pData->IsCaseSensitive();
             nShowList = pData->GetListType();
             bShowInput = pData->GetInput( aInputTitle, aInputMessage );
             ScValidErrorStyle eStyle;
@@ -593,6 +595,7 @@ ScValidationData* ScTableValidationObj::CreateValidationData( ScDocument& rDoc,
                                                    maExprNmsp1, maExprNmsp2,
                                                    eGrammar1, eGrammar2 );
     pRet->SetIgnoreBlank(bIgnoreBlank);
+    pRet->SetCaseSensitive(bCaseSensitive);
     pRet->SetListType(nShowList);
 
     if ( aTokens1.hasElements() )
@@ -628,6 +631,7 @@ void ScTableValidationObj::ClearData_Impl()
     nMode        = ScConditionMode::NONE;
     nValMode     = SC_VALID_ANY;
     bIgnoreBlank = true;
+    bCaseSensitive = false;
     nShowList    = sheet::TableValidationVisibility::UNSORTED;
     bShowInput   = false;
     bShowError   = false;
@@ -762,6 +766,7 @@ void SAL_CALL ScTableValidationObj::setPropertyValue(
     if ( aPropertyName == SC_UNONAME_SHOWINP )       bShowInput = ScUnoHelpFunctions::GetBoolFromAny( aValue );
     else if ( aPropertyName == SC_UNONAME_SHOWERR )  bShowError = ScUnoHelpFunctions::GetBoolFromAny( aValue );
     else if ( aPropertyName == SC_UNONAME_IGNOREBL ) bIgnoreBlank = ScUnoHelpFunctions::GetBoolFromAny( aValue );
+    else if ( aPropertyName == SC_UNONAME_ISCASE )   bCaseSensitive = ScUnoHelpFunctions::GetBoolFromAny( aValue );
     else if ( aPropertyName == SC_UNONAME_SHOWLIST ) aValue >>= nShowList;
     else if ( aPropertyName == SC_UNONAME_INPTITLE )
     {
@@ -871,6 +876,7 @@ uno::Any SAL_CALL ScTableValidationObj::getPropertyValue( const OUString& aPrope
     if ( aPropertyName == SC_UNONAME_SHOWINP )       aRet <<= bShowInput;
     else if ( aPropertyName == SC_UNONAME_SHOWERR )  aRet <<= bShowError;
     else if ( aPropertyName == SC_UNONAME_IGNOREBL ) aRet <<= bIgnoreBlank;
+    else if ( aPropertyName == SC_UNONAME_ISCASE )   aRet <<= bCaseSensitive;
     else if ( aPropertyName == SC_UNONAME_SHOWLIST ) aRet <<= nShowList;
     else if ( aPropertyName == SC_UNONAME_INPTITLE ) aRet <<= aInputTitle;
     else if ( aPropertyName == SC_UNONAME_INPMESS )  aRet <<= aInputMessage;
diff --git a/sc/source/ui/vba/vbavalidation.cxx b/sc/source/ui/vba/vbavalidation.cxx
index a30b43fe734a..119536109269 100644
--- a/sc/source/ui/vba/vbavalidation.cxx
+++ b/sc/source/ui/vba/vbavalidation.cxx
@@ -69,6 +69,23 @@ ScVbaValidation::setIgnoreBlank( sal_Bool _ignoreblank )
 }
 
 sal_Bool SAL_CALL
+ScVbaValidation::getCaseSensitive()
+{
+    uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
+    bool bCase = false;
+    xProps->getPropertyValue( SC_UNONAME_ISCASE )  >>= bCase;
+    return bCase;
+}
+
+void SAL_CALL
+ScVbaValidation::setCaseSensitive( sal_Bool _bCase )
+{
+    uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
+    xProps->setPropertyValue( SC_UNONAME_ISCASE, uno::Any( _bCase ) );
+    lcl_setValidationProps( m_xRange, xProps );
+}
+
+sal_Bool SAL_CALL
 ScVbaValidation::getInCellDropdown()
 {
     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
@@ -197,6 +214,7 @@ ScVbaValidation::Delete(  )
     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
     uno::Reference< sheet::XSheetCondition > xCond( xProps, uno::UNO_QUERY_THROW );
     xProps->setPropertyValue( SC_UNONAME_IGNOREBL, uno::Any( true ) );
+    xProps->setPropertyValue( SC_UNONAME_ISCASE, uno::Any( false ) );
     xProps->setPropertyValue( SC_UNONAME_SHOWINP, uno::Any( true ) );
     xProps->setPropertyValue( SC_UNONAME_SHOWERR, uno::Any( true ) );
     xProps->setPropertyValue( SC_UNONAME_ERRTITLE, uno::Any( sBlank ) );
diff --git a/sc/source/ui/vba/vbavalidation.hxx b/sc/source/ui/vba/vbavalidation.hxx
index 6395d6728ad7..8e2082f7a483 100644
--- a/sc/source/ui/vba/vbavalidation.hxx
+++ b/sc/source/ui/vba/vbavalidation.hxx
@@ -36,6 +36,8 @@ public:
     // Attributes
     virtual sal_Bool SAL_CALL getIgnoreBlank() override;
     virtual void SAL_CALL setIgnoreBlank( sal_Bool _ignoreblank ) override;
+    virtual sal_Bool SAL_CALL getCaseSensitive() override;
+    virtual void SAL_CALL setCaseSensitive( sal_Bool _bCase ) override;
     virtual sal_Bool SAL_CALL getInCellDropdown() override;
     virtual void SAL_CALL setInCellDropdown( sal_Bool _incelldropdown ) override;
     virtual sal_Bool SAL_CALL getShowInput() override;
diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx
index f4a932fff402..594186f4120e 100644
--- a/sc/source/ui/view/cellsh2.cxx
+++ b/sc/source/ui/view/cellsh2.cxx
@@ -849,6 +849,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                     ScConditionMode eOper = ScConditionMode::Equal;
                     OUString aExpr1, aExpr2;
                     bool bBlank = true;
+                    bool bCaseSensitive = false;
                     sal_Int16 nListType = css::sheet::TableValidationVisibility::UNSORTED;
                     bool bShowHelp = false;
                     OUString aHelpTitle, aHelpText;
@@ -881,6 +882,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                             aExpr1 = pOldData->GetExpression( aCursorPos, 0, nNumFmt );
                             aExpr2 = pOldData->GetExpression( aCursorPos, 1, nNumFmt );
                             bBlank = pOldData->IsIgnoreBlank();
+                            bCaseSensitive = pOldData->IsCaseSensitive();
                             nListType = pOldData->GetListType();
 
                             bShowHelp = pOldData->GetInput( aHelpTitle, aHelpText );
@@ -891,6 +893,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                             aArgSet.Put( SfxStringItem(  FID_VALID_VALUE1,      aExpr1 ) );
                             aArgSet.Put( SfxStringItem(  FID_VALID_VALUE2,      aExpr2 ) );
                             aArgSet.Put( SfxBoolItem(    FID_VALID_BLANK,       bBlank ) );
+                            aArgSet.Put( SfxBoolItem(    FID_VALID_CASESENS,    bCaseSensitive ) );
                             aArgSet.Put( SfxInt16Item(   FID_VALID_LISTTYPE,    nListType ) );
                             aArgSet.Put( SfxBoolItem(    FID_VALID_SHOWHELP,    bShowHelp ) );
                             aArgSet.Put( SfxStringItem(  FID_VALID_HELPTITLE,   aHelpTitle ) );
@@ -966,6 +969,8 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                         }
                         if ( const SfxBoolItem* pItem = pOutSet->GetItemIfSet( FID_VALID_BLANK ) )
                             bBlank = pItem->GetValue();
+                        if ( const SfxBoolItem* pItem = pOutSet->GetItemIfSet( FID_VALID_CASESENS ) )
+                            bCaseSensitive = pItem->GetValue();
                         if ( const SfxInt16Item* pItem = pOutSet->GetItemIfSet( FID_VALID_LISTTYPE ) )
                             nListType = pItem->GetValue();
 
@@ -987,6 +992,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
 
                         ScValidationData aData( eMode, eOper, aExpr1, aExpr2, rDoc, aCursorPos );
                         aData.SetIgnoreBlank( bBlank );
+                        aData.SetCaseSensitive( bCaseSensitive );
                         aData.SetListType( nListType );
 
                         aData.SetInput(aHelpTitle, aHelpText);          // sets bShowInput to TRUE
diff --git a/sc/uiconfig/scalc/ui/validationcriteriapage.ui b/sc/uiconfig/scalc/ui/validationcriteriapage.ui
index e18aaa9619e1..5703ae0e2e4a 100644
--- a/sc/uiconfig/scalc/ui/validationcriteriapage.ui
+++ b/sc/uiconfig/scalc/ui/validationcriteriapage.ui
@@ -2,7 +2,7 @@
 <!-- Generated with glade 3.40.0 -->
 <interface domain="sc">
   <requires lib="gtk+" version="3.20"/>
-  <!-- n-columns=2 n-rows=8 -->
+  <!-- n-columns=2 n-rows=9 -->
   <object class="GtkGrid" id="ValidationCriteriaPage">
     <property name="visible">True</property>
     <property name="can-focus">False</property>
@@ -36,7 +36,7 @@
       </object>
       <packing>
         <property name="left-attach">0</property>
-        <property name="top-attach">4</property>
+        <property name="top-attach">5</property>
       </packing>
     </child>
     <child>
@@ -87,7 +87,7 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">4</property>
+        <property name="top-attach">5</property>
       </packing>
     </child>
     <child>
@@ -101,7 +101,7 @@
       </object>
       <packing>
         <property name="left-attach">0</property>
-        <property name="top-attach">5</property>
+        <property name="top-attach">6</property>
       </packing>
     </child>
     <child>
@@ -183,7 +183,7 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">5</property>
+        <property name="top-attach">6</property>
       </packing>
     </child>
     <child>
@@ -197,7 +197,7 @@
       </object>
       <packing>
         <property name="left-attach">0</property>
-        <property name="top-attach">7</property>
+        <property name="top-attach">8</property>
       </packing>
     </child>
     <child>
@@ -235,7 +235,7 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">2</property>
+        <property name="top-attach">3</property>
       </packing>
     </child>
     <child>
@@ -256,7 +256,7 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">3</property>
+        <property name="top-attach">4</property>
       </packing>
     </child>
     <child>
@@ -274,7 +274,7 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">6</property>
+        <property name="top-attach">7</property>
       </packing>
     </child>
     <child>
@@ -292,10 +292,32 @@
       </object>
       <packing>
         <property name="left-attach">1</property>
-        <property name="top-attach">7</property>
+        <property name="top-attach">8</property>
       </packing>
     </child>
     <child>
+      <object class="GtkCheckButton" id="casesens">
+        <property name="label" translatable="yes" context="validationcriteriapage|allowempty">Case _sensitive</property>
+        <property name="visible">True</property>
+        <property name="can-focus">True</property>
+        <property name="receives-default">False</property>
+        <property name="use-underline">True</property>
+        <property name="draw-indicator">True</property>
+        <child internal-child="accessible">
+          <object class="AtkObject" id="casesens-atkobject">
+            <property name="AtkObject::accessible-description" translatable="yes" context="validationcriteriapage|extended_tip|allowempty">In conjunction with Tools - Detective - Mark invalid Data, this defines that blank cells are shown as invalid data (disabled) or not (enabled).</property>
+          </object>
+        </child>
+      </object>
+      <packing>
+        <property name="left-attach">1</property>
+        <property name="top-attach">2</property>
+      </packing>
+    </child>
+    <child>
+      <placeholder/>
+    </child>
+    <child>
       <placeholder/>
     </child>
     <child>
diff --git a/test/source/sheet/tablevalidation.cxx b/test/source/sheet/tablevalidation.cxx
index 4383019602e5..3069c6c06553 100644
--- a/test/source/sheet/tablevalidation.cxx
+++ b/test/source/sheet/tablevalidation.cxx
@@ -124,6 +124,16 @@ void TableValidation::testTableValidationProperties()
     CPPUNIT_ASSERT(xTableValidation->getPropertyValue(propName) >>= aIgnoreBlankCells);
     CPPUNIT_ASSERT_MESSAGE("Unable to set property value IgnoreBlankCells", !aIgnoreBlankCells);
 
+    propName = "IsCaseSensitive";
+    bool bCaseSensitive = false;
+    CPPUNIT_ASSERT(xTableValidation->getPropertyValue(propName) >>= bCaseSensitive);
+    CPPUNIT_ASSERT_MESSAGE("Unable to get property value IsCaseSensitive", !bCaseSensitive);
+
+    aNewValue <<= true;
+    xTableValidation->setPropertyValue(propName, aNewValue);
+    CPPUNIT_ASSERT(xTableValidation->getPropertyValue(propName) >>= bCaseSensitive);
+    CPPUNIT_ASSERT_MESSAGE("Unable to set property value IsCaseSensitive", bCaseSensitive);
+
     propName = "ErrorAlertStyle";
     aValue = xTableValidation->getPropertyValue(propName);
     sheet::ValidationAlertStyle aValidationAlertStyle;