CWS-TOOLING: integrate CWS validityref

19 files changed, 1133 insertions, 111 deletions

diff --git a/sc/source/ui/app/scdll.cxx b/sc/source/ui/app/scdll.cxx
index 8bae4f1762d4..2be737d9a717 100644
--- a/sc/source/ui/app/scdll.cxx
+++ b/sc/source/ui/app/scdll.cxx
@@ -328,6 +328,10 @@ void ScDLL::Init()
     ScSpellDialogChildWindow    ::RegisterChildWindow(FALSE, pMod);
     ::avmedia::MediaPlayer      ::RegisterChildWindow(FALSE, pMod);
 
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    ScValidityRefChildWin::RegisterChildWindow(FALSE, pMod);
+    //-->Added by PengYunQuan for Validity Cell Range Picker
+
     //  Edit-Engine-Felder, soweit nicht schon in OfficeApplication::Init
 
     SvClassManager& rClassManager = SvxFieldItem::GetClassManager();
diff --git a/sc/source/ui/app/scmod.cxx b/sc/source/ui/app/scmod.cxx
index 5afe9e11c7d5..fb31b5601cf8 100644
--- a/sc/source/ui/app/scmod.cxx
+++ b/sc/source/ui/app/scmod.cxx
@@ -2216,4 +2216,82 @@ IMPL_LINK( ScModule, CalcFieldValueHdl, EditFieldInfo*, pInfo )
 
 
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+BOOL ScModule::RegisterRefWindow( USHORT nSlotId, Window *pWnd )
+{
+    std::list<Window*> & rlRefWindow = m_mapRefWindow[nSlotId];
+
+    if( std::find( rlRefWindow.begin(), rlRefWindow.end(), pWnd ) == rlRefWindow.end() )
+    {
+        rlRefWindow.push_back( pWnd );
+        return TRUE;
+    }
+
+    return FALSE;
+}
+
+BOOL  ScModule::UnregisterRefWindow( USHORT nSlotId, Window *pWnd )
+{
+    std::map<USHORT, std::list<Window*> >::iterator iSlot = m_mapRefWindow.find( nSlotId );
+
+    if( iSlot == m_mapRefWindow.end() )
+        return FALSE;
+
+    std::list<Window*> & rlRefWindow = iSlot->second;
+
+    std::list<Window*>::iterator i = std::find( rlRefWindow.begin(), rlRefWindow.end(), pWnd );
+
+    if( i == rlRefWindow.end() )
+        return FALSE;
+
+    rlRefWindow.erase( i );
+
+    if( !rlRefWindow.size() )
+        m_mapRefWindow.erase( nSlotId );
+
+    return TRUE;
+}
+
+BOOL  ScModule::IsAliveRefDlg( USHORT nSlotId, Window *pWnd )
+{
+    std::map<USHORT, std::list<Window*> >::iterator iSlot = m_mapRefWindow.find( nSlotId );
 
+    if( iSlot == m_mapRefWindow.end() )
+        return FALSE;
+
+    std::list<Window*> & rlRefWindow = iSlot->second;
+
+    return rlRefWindow.end() != std::find( rlRefWindow.begin(), rlRefWindow.end(), pWnd );
+}
+
+Window *  ScModule::Find1RefWindow( USHORT nSlotId, Window *pWndAncestor )
+{
+    std::map<USHORT, std::list<Window*> >::iterator iSlot = m_mapRefWindow.find( nSlotId );
+
+    if( iSlot == m_mapRefWindow.end() )
+        return FALSE;
+
+    std::list<Window*> & rlRefWindow = iSlot->second;
+
+    while( Window *pParent = pWndAncestor->GetParent() ) pWndAncestor = pParent;
+
+    for( std::list<Window*>::iterator i = rlRefWindow.begin(); i!=rlRefWindow.end(); i++ )
+        if ( pWndAncestor->IsWindowOrChild( *i, (*i)->IsSystemWindow() ) )
+            return *i;
+
+    return NULL;
+}
+
+Window *  ScModule::Find1RefWindow( Window *pWndAncestor )
+{
+    while( Window *pParent = pWndAncestor->GetParent() ) pWndAncestor = pParent;
+
+    for( std::map<USHORT, std::list<Window*> >::iterator i = m_mapRefWindow.begin();
+        i!=m_mapRefWindow.end(); i++ )
+        for( std::list<Window*>::iterator j = i->second.begin(); j!=i->second.end(); j++ )
+            if ( pWndAncestor->IsWindowOrChild( *j, (*j)->IsSystemWindow() ) )
+                return *j;
+
+    return NULL;
+}
+//<!--Added by PengYunQuan for Validity Cell Range Picker
diff --git a/sc/source/ui/attrdlg/scdlgfact.cxx b/sc/source/ui/attrdlg/scdlgfact.cxx
index 60b101d482c0..8d5e0e1e6791 100644
--- a/sc/source/ui/attrdlg/scdlgfact.cxx
+++ b/sc/source/ui/attrdlg/scdlgfact.cxx
@@ -1425,13 +1425,19 @@ SfxAbstractTabDialog * ScAbstractDialogFactory_Impl::CreateScParagraphDlg( Windo
 
 //add for ScValidationDlg begin
 SfxAbstractTabDialog * ScAbstractDialogFactory_Impl::CreateScValidationDlg( Window* pParent,
-                                                        const SfxItemSet* pArgSet,int nId  )
+//<!--Modified by PengYunQuan for Validity Cell Range Picker
+//                                                      const SfxItemSet* pArgSet,int nId  )
+                                                        const SfxItemSet* pArgSet,int nId, ScTabViewShell *pTabVwSh  )
+//-->Modified by PengYunQuan for Validity Cell Range Picke
 {
     SfxTabDialog* pDlg=NULL;
     switch ( nId )
     {
         case TAB_DLG_VALIDATION :
-            pDlg = new ScValidationDlg( pParent, pArgSet );
+            //<!--Modified by PengYunQuan for Validity Cell Range Picker
+            //pDlg = new ScValidationDlg( pParent, pArgSet );
+            pDlg = new ScValidationDlg( pParent, pArgSet, pTabVwSh );
+            //-->Modified by PengYunQuan for Validity Cell Range Picker
             break;
         default:
             break;
diff --git a/sc/source/ui/attrdlg/scdlgfact.hxx b/sc/source/ui/attrdlg/scdlgfact.hxx
index 40f6d3bee976..530220858f35 100644
--- a/sc/source/ui/attrdlg/scdlgfact.hxx
+++ b/sc/source/ui/attrdlg/scdlgfact.hxx
@@ -546,7 +546,10 @@ public:
                                                             int nId );
 
     virtual SfxAbstractTabDialog * CreateScValidationDlg( Window* pParent, //add for ScValidationDlg
-                                                        const SfxItemSet* pArgSet,int nId  );
+    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+    //                                                  const SfxItemSet* pArgSet,int nId  );
+                                                        const SfxItemSet* pArgSet,int nId, ScTabViewShell *pTabVwSh  );
+    //-->Modified by PengYunQuan for Validity Cell Range Picker
 
     virtual SfxAbstractTabDialog * CreateScSortDlg( Window*          pParent, //add for ScSortDlg
                                                     const SfxItemSet* pArgSet,int nId );
diff --git a/sc/source/ui/dbgui/makefile.mk b/sc/source/ui/dbgui/makefile.mk
index 5d716552ca71..b495b5eabcc9 100644
--- a/sc/source/ui/dbgui/makefile.mk
+++ b/sc/source/ui/dbgui/makefile.mk
@@ -81,7 +81,8 @@ EXCEPTIONSFILES= \
     $(SLO)$/csvtablebox.obj \
     $(SLO)$/fieldwnd.obj \
     $(SLO)$/pvlaydlg.obj	\
-    $(SLO)$/dapidata.obj
+    $(SLO)$/dapidata.obj	\
+    $(SLO)$/validate.obj
 
 SRS1NAME=$(TARGET)
 SRC1FILES =  \
diff --git a/sc/source/ui/dbgui/validate.cxx b/sc/source/ui/dbgui/validate.cxx
index 8700c936e0f5..4351d357abb9 100644
--- a/sc/source/ui/dbgui/validate.cxx
+++ b/sc/source/ui/dbgui/validate.cxx
@@ -53,6 +53,12 @@
 #include "validate.hxx"
 #include "compiler.hxx"
 #include "formula/opcode.hxx" //CHINA001
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+#include "tabvwsh.hxx"
+#include <sfx2/viewfrm.hxx>
+#include <sfx2/childwin.hxx>
+#include "reffact.hxx"
+//-->Added by PengYunQuan for Validity Cell Range Picker
 
 // ============================================================================
 
@@ -66,8 +72,19 @@ static USHORT pValueRanges[] =
 
 // ============================================================================
 
-ScValidationDlg::ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet ) :
-    SfxTabDialog( pParent, ScResId( TAB_DLG_VALIDATION ), pArgSet )
+//<!--Modified by PengYunQuan for Validity Cell Range Picker
+//ScValidationDlg::ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet ) :
+//    SfxTabDialog( pParent, ScResId( TAB_DLG_VALIDATION ), pArgSet )
+ScValidationDlg::ScValidationDlg( Window*           pParent,
+                                 const SfxItemSet* pArgSet,
+                                 ScTabViewShell *pTabViewSh,
+                                 SfxBindings *pB /*= NULL*/
+                      ) :
+        ScValidationDlgBase( pParent ? pParent : SFX_APP()->GetTopWindow(), TAB_DLG_VALIDATION, pArgSet, pB ),
+            m_bOwnRefHdlr( false ),
+            m_pTabVwSh( pTabViewSh ),
+            m_bRefInputting( false )
+//-->Modified by PengYunQuan for Validity Cell Range Picker
 {
     AddTabPage( TP_VALIDATION_VALUES,    ScTPValidationValue::Create, 0 );
     AddTabPage( TP_VALIDATION_INPUTHELP, ScTPValidationHelp::Create,  0 );
@@ -75,8 +92,100 @@ ScValidationDlg::ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet ) :
     FreeResource();
 }
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+void ScTPValidationValue::SetReferenceHdl( const ScRange&rRange , ScDocument* pDoc )
+{
+    if ( rRange.aStart != rRange.aEnd )
+        if ( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+            if( m_pRefEdit )
+                pValidationDlg/*->GetRefHandler()*/->RefInputStart( m_pRefEdit );
+
+    if ( m_pRefEdit )
+    {
+        String  aStr;
+        rRange.Format( aStr, SCR_ABS_3D, pDoc );
+        m_pRefEdit->SetRefString( aStr );
+        //m_pRefEdit->SetRefString( rRange.aStart != rRange.aEnd ? aStr : String::CreateFromAscii("=").Append( aStr ) );
+    }
+}
+
+void ScTPValidationValue:: SetActiveHdl()
+{
+    if ( m_pRefEdit ) m_pRefEdit->GrabFocus();
+
+    if ( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+        if( m_pRefEdit )
+        {
+            pValidationDlg/*->GetRefHandler()*/->RefInputDone();
+        }
+}
+
+void            ScTPValidationValue::RefInputStartPreHdl( ScRefEdit* pEdit, ScRefButton* pButton )
+{
+    if ( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+    {
+        if( pEdit == m_pRefEdit )
+        {
+            if( Window *pPreWnd = pEdit==&maEdMax?&maFtMax:(pEdit==&maEdMin?&maFtMin:NULL) )
+            {
+                pPreWnd->SetParent( pValidationDlg );
+                pPreWnd->Hide();
+            }
+
+            m_pRefEdit->SetParent( pValidationDlg );
+        }
+
+        if( pButton == &m_btnRef )m_btnRef.SetParent( pValidationDlg );
+    }
+}
+
+void            ScTPValidationValue::RefInputDonePreHdl()
+{
+
+    if( m_pRefEdit && m_pRefEdit->GetParent()!= this )
+    {
+        if( Window *pPreWnd = m_pRefEdit==&maEdMax?&maFtMax:(m_pRefEdit==&maEdMin?&maFtMin:NULL) )
+        {
+            pPreWnd->SetParent( this );
+            pPreWnd->Show();
+        }
+
+        m_pRefEdit->SetParent( this );
+
+        m_btnRef.SetParent( m_pRefEdit ); //if Edit SetParent but button not, the tab order will be incorrect, need button to setparent to anthor window and restore parent later in order to restore the tab order
+//      aExample1.SetParent( m_pRefEdit ); // the aExample1's child order will affect acc key
+    }
+
+    if( m_btnRef.GetParent()!=this ) m_btnRef.SetParent( this );
+//  if( aExample1.GetParent()!=this ) aExample1.SetParent( this );
+}
+
+void            ScTPValidationValue::RefInputDonePostHdl()
+{
+
+
+    if( m_pRefEdit && !m_pRefEdit->HasFocus() )
+        m_pRefEdit->GrabFocus();
+
+}
+
+
+BOOL ScValidationDlg::Close()
+{
+    if( m_bOwnRefHdlr )
+        if( SfxTabPage* pPage = GetTabPage( TP_VALIDATION_VALUES ) )
+            static_cast<ScTPValidationValue*>(pPage)->RemoveRefDlg();
+
+    return ScValidationDlgBase::Close();
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
+
 ScValidationDlg::~ScValidationDlg()
 {
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    if( m_bOwnRefHdlr )
+        RemoveRefDlg( FALSE );
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 }
 
 
@@ -236,7 +345,10 @@ ScTPValidationValue::ScTPValidationValue( Window* pParent, const SfxItemSet& rAr
     maStrMax  ( ScResId( SCSTR_VALID_MAXIMUM ) ),
     maStrValue( ScResId( SCSTR_VALID_VALUE ) ),
     maStrRange( ScResId( SCSTR_VALID_RANGE ) ),
-    maStrList ( ScResId( SCSTR_VALID_LIST ) )
+    maStrList ( ScResId( SCSTR_VALID_LIST ) ),
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+        m_btnRef( this, ScResId( RB_VALIDITY_REF ) )
+//-->Added by PengYunQuan for Validity Cell Range Picker
 {
     Init();
     FreeResource();
@@ -246,6 +358,9 @@ ScTPValidationValue::ScTPValidationValue( Window* pParent, const SfxItemSet& rAr
     String aListSep = ::GetScCompilerNativeSymbol( ocSep ); //CHINA001
     DBG_ASSERT( aListSep.Len() == 1, "ScTPValidationValue::ScTPValidationValue - list separator error" );
     mcFmlaSep = aListSep.Len() ? aListSep.GetChar( 0 ) : ';';
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    m_btnRef.Hide();
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 }
 
 ScTPValidationValue::~ScTPValidationValue()
@@ -257,6 +372,13 @@ void ScTPValidationValue::Init()
     maLbAllow.SetSelectHdl( LINK( this, ScTPValidationValue, SelectHdl ) );
     maLbValue.SetSelectHdl( LINK( this, ScTPValidationValue, SelectHdl ) );
     maCbShow.SetClickHdl( LINK( this, ScTPValidationValue, CheckHdl ) );
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    maEdMin.SetGetFocusHdl( LINK( this, ScTPValidationValue, EditSetFocusHdl ) );
+    maEdMin.SetLoseFocusHdl( LINK( this, ScTPValidationValue, KillFocusHdl ) );
+    maEdMax.SetGetFocusHdl( LINK( this, ScTPValidationValue, EditSetFocusHdl ) );
+    m_btnRef.SetLoseFocusHdl( LINK( this, ScTPValidationValue, KillFocusHdl ) );
+    maEdMax.SetLoseFocusHdl( LINK( this, ScTPValidationValue, KillFocusHdl ) );
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 
     maLbAllow.SelectEntryPos( SC_VALIDDLG_ALLOW_ANY );
     maLbValue.SelectEntryPos( SC_VALIDDLG_DATA_EQUAL );
@@ -374,6 +496,133 @@ void ScTPValidationValue::SetSecondFormula( const String& rFmlaStr )
     maEdMax.SetText( rFmlaStr );
 }
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+ScValidationDlg * ScTPValidationValue::GetValidationDlg()
+{
+    if( Window *pParent = GetParent() )
+        do{
+            if ( dynamic_cast<ScValidationDlg*>( pParent ) )
+                return static_cast< ScValidationDlg * >( pParent );
+        }while ( NULL != ( pParent = pParent->GetParent() ) );
+    return NULL;
+}
+void ScTPValidationValue::SetupRefDlg()
+{
+    if( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+        if( pValidationDlg->SetupRefDlg() )
+        {
+            pValidationDlg->SetHandler( this );
+            pValidationDlg->SetSetRefHdl( (ScRefHandlerHelper::PFUNCSETREFHDLTYPE)( &ScTPValidationValue::SetReferenceHdl ) );
+            pValidationDlg->SetSetActHdl( (ScRefHandlerHelper::PCOMMONHDLTYPE)( &ScTPValidationValue::SetActiveHdl ) );
+            pValidationDlg->SetRefInputStartPreHdl( (ScRefHandlerHelper::PINPUTSTARTDLTYPE)( &ScTPValidationValue::RefInputStartPreHdl ) );
+            pValidationDlg->SetRefInputDonePreHdl( (ScRefHandlerHelper::PCOMMONHDLTYPE)( &ScTPValidationValue::RefInputDonePreHdl ) );
+            pValidationDlg->SetRefInputDonePostHdl( (ScRefHandlerHelper::PCOMMONHDLTYPE)( &ScTPValidationValue::RefInputDonePostHdl ) );
+
+            if ( maEdMax.IsVisible() ) { m_pRefEdit = &maEdMax; }
+            else if ( maEdMin.IsVisible() ) {  m_pRefEdit = &maEdMin; }
+
+            if( m_pRefEdit && !m_pRefEdit->HasFocus() ) m_pRefEdit->GrabFocus();
+
+            if( m_pRefEdit ) m_pRefEdit->SetRefDialog( pValidationDlg );
+            m_btnRef.SetReferences( pValidationDlg, m_pRefEdit );
+        }
+}
+
+void ScTPValidationValue::RemoveRefDlg()
+{
+    if( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+        if( pValidationDlg->RemoveRefDlg() )
+        {
+            pValidationDlg->SetHandler( NULL );
+            pValidationDlg->SetSetRefHdl( NULL );
+            pValidationDlg->SetSetActHdl( NULL );
+            pValidationDlg->SetRefInputStartPreHdl( NULL );
+            pValidationDlg->SetRefInputDonePreHdl( NULL );
+            pValidationDlg->SetRefInputDonePostHdl( NULL );
+
+            if( m_pRefEdit ) m_pRefEdit->SetRefDialog( NULL );
+            m_pRefEdit = NULL;
+
+            m_btnRef.SetReferences( NULL, NULL );
+
+#if ! defined( WNT ) && !defined( _MSC_VER )
+            TidyListBoxes();
+#endif
+        }
+}
+
+void ScTPValidationValue::TidyListBoxes()
+{
+    if ( Window *pWnd = GetChild( 0 ) )
+    {
+        bool bFindLst = false;
+        std::list<Window*> alstOrder;
+
+        do{
+            if( pWnd->GetParent() == this )
+            {
+                if ( !bFindLst )
+                {
+                    try{
+                        if( dynamic_cast<ListBox*>(pWnd)||dynamic_cast<ListBox*>(pWnd->GetWindow(WINDOW_CLIENT) ) )
+                            bFindLst = true;
+                    }
+                    catch( ... )
+                    {
+                        if ( *(void**)pWnd == *(void**)&maLbValue )
+                            bFindLst = true;
+                        else if ( Window *pClient = pWnd->GetWindow( WINDOW_CLIENT ) )
+                            if ( *(void**)pClient == *(void**)&maLbValue )
+                                bFindLst = true;
+                    }
+                }
+
+                if ( bFindLst )
+                    alstOrder.push_back( pWnd->GetWindow( WINDOW_CLIENT ) );
+            }
+        }while( NULL != ( pWnd = pWnd->GetWindow( WINDOW_NEXT  ) ) );
+
+        pWnd = GetChild(0);
+
+        while( std::find( alstOrder.begin(), alstOrder.end(), pWnd ) != alstOrder.end() && NULL != ( pWnd = pWnd->GetWindow( WINDOW_NEXT) ) );
+
+        if ( pWnd )
+        {
+            for ( std::list<Window*>::iterator i = alstOrder.begin(); i!=alstOrder.end(); i++ )
+            {
+                Window *pParent = (*i)->GetParent();
+                (*i)->SetParent( pWnd );
+                (*i)->SetParent( pParent );
+            }
+        }
+    }
+}
+
+IMPL_LINK( ScTPValidationValue, EditSetFocusHdl, Edit *, /*pEdit*/ )
+{
+    USHORT  nPos=maLbAllow.GetSelectEntryPos();
+
+    if ( nPos == SC_VALIDDLG_ALLOW_RANGE )
+    {
+        SetupRefDlg();
+    }
+
+    return 0;
+}
+
+IMPL_LINK( ScTPValidationValue, KillFocusHdl, Window *, pWnd )
+{
+    if( pWnd == m_pRefEdit || pWnd == &m_btnRef )
+        if( ScValidationDlg *pValidationDlg = GetValidationDlg() )
+            if ( (pValidationDlg->IsActive() || pValidationDlg->IsChildFocus() ) && !pValidationDlg->IsRefInputting() )
+                if( ( !m_pRefEdit || !m_pRefEdit->HasFocus()) && !m_btnRef.HasFocus() )
+                {
+                    RemoveRefDlg();
+                }
+
+    return 0;
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
 
 // ----------------------------------------------------------------------------
 
@@ -427,7 +676,9 @@ IMPL_LINK( ScTPValidationValue, SelectHdl, ListBox*, EMPTYARG )
     maFtMax.Show( bShowMax );
     maEdMax.Show( bShowMax );
     maFtHint.Show( bRange );
-
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    m_btnRef.Show( bRange );
+    //-->Added by PengYunQuan for Validity Cell Range Picker
     return 0;
 }
 
@@ -660,3 +911,114 @@ IMPL_LINK( ScTPValidationError, ClickSearchHdl, PushButton*, EMPTYARG )
     return( 0L );
 }
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+bool ScValidationDlg::EnterRefStatus()
+{
+    ScTabViewShell *pTabViewShell = GetTabViewShell();
+
+    if( !pTabViewShell ) return false;
+
+    USHORT nId  = SLOTID;
+    SfxViewFrame* pViewFrm = pTabViewShell->GetViewFrame();
+    SfxChildWindow* pWnd = pViewFrm->GetChildWindow( nId );
+
+    if ( pWnd && pWnd->GetWindow()!= this ) pWnd = NULL;
+
+    SC_MOD()->SetRefDialog( nId, pWnd ? FALSE : TRUE );
+
+    return true;
+}
+
+bool ScValidationDlg::LeaveRefStatus()
+{
+    ScTabViewShell *pTabViewShell = GetTabViewShell();
+
+    if( !pTabViewShell ) return false;
+
+    USHORT nId  = SLOTID;
+    SfxViewFrame* pViewFrm = pTabViewShell->GetViewFrame();
+    //SfxChildWindow* pWnd = pViewFrm->GetChildWindow( nId );
+    if ( pViewFrm->GetChildWindow( nId ) )
+    {
+        DoClose( nId );
+    }
+    return true;
+}
+
+bool ScValidationDlg::SetupRefDlg()
+{
+    if ( m_bOwnRefHdlr ) return false;
+    if( EnterRefMode() )
+    {
+        SetModal( FALSE );
+        return  /*SetChkShell( GetDocShell() ),*/ m_bOwnRefHdlr = true && EnterRefStatus();
+    }
+
+    return false;
+}
+
+bool ScValidationDlg::RemoveRefDlg( BOOL bRestoreModal /* = TRUE */ )
+{
+    bool bVisLock = false;
+    bool bFreeWindowLock = false;
+
+    ScTabViewShell *pTabVwSh = GetTabViewShell();
+
+    if( !pTabVwSh ) return false;
+
+    if ( SfxChildWindow* pWnd = pTabVwSh->GetViewFrame()->GetChildWindow( SID_VALIDITY_REFERENCE ) )
+    {
+        bVisLock = static_cast<ScValidityRefChildWin*>(pWnd)->LockVisible( true );
+        bFreeWindowLock = static_cast<ScValidityRefChildWin*>(pWnd)->LockFreeWindow( true );
+    }
+
+    if ( !m_bOwnRefHdlr ) return false;
+    if( LeaveRefStatus() && LeaveRefMode() )
+    {
+        m_bOwnRefHdlr = false;
+
+        if( bRestoreModal )
+            SetModal( TRUE );
+    }
+
+    if ( SfxChildWindow* pWnd = pTabVwSh->GetViewFrame()->GetChildWindow( SID_VALIDITY_REFERENCE ) )
+    {
+        static_cast<ScValidityRefChildWin*>(pWnd)->LockVisible( bVisLock );
+        static_cast<ScValidityRefChildWin*>(pWnd)->LockFreeWindow( bFreeWindowLock );
+    }
+
+    return true;
+}
+
+//TYPEINIT1( ScTPValidationValue, SfxTabPage )
+
+void ScTPValidationValue::ScRefButtonEx::Click()
+{
+    if( ScTPValidationValue *pParent = dynamic_cast< ScTPValidationValue*>( GetParent() ) )
+        pParent->OnClick( this );
+
+    ScRefButton::Click();
+}
+
+void ScTPValidationValue::OnClick( Button *pBtn )
+{
+    if( pBtn == &m_btnRef )
+        SetupRefDlg();
+}
+
+BOOL ScValidationDlg::IsChildFocus()
+{
+    if ( const Window *pWin = Application::GetFocusWindow() )
+        while( NULL != ( pWin = pWin->GetParent() ) )
+            if( pWin == this )
+                return TRUE;
+
+    return FALSE;
+}
+
+
+bool    ScValidationDlg::IsAlive()
+{
+    return SC_MOD()->IsAliveRefDlg( SLOTID, this );
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
diff --git a/sc/source/ui/dbgui/validate.src b/sc/source/ui/dbgui/validate.src
index afd3fc213391..1f8e4b48deac 100644
--- a/sc/source/ui/dbgui/validate.src
+++ b/sc/source/ui/dbgui/validate.src
@@ -65,7 +65,9 @@ TabDialog TAB_DLG_VALIDATION
     };
     Text [ en-US ] = "Validity" ;
 };
-
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+#define OFFSET_X    30
+//-->Added by PengYunQuan for Validity Cell Range Picker
 TabPage TP_VALIDATION_VALUES
 {
     Hide = TRUE ;
@@ -75,13 +77,19 @@ TabPage TP_VALIDATION_VALUES
     FixedText FT_ALLOW
     {
         Pos = MAP_APPFONT ( 6 , 16 ) ;
-        Size = MAP_APPFONT ( 70 , 8 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Size = MAP_APPFONT ( 70 , 8 ) ;
+        Size = MAP_APPFONT ( 70 - OFFSET_X , 8 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Text [ en-US ] = "~Allow" ;
     };
     ListBox LB_ALLOW
     {
         Border = TRUE ;
-        Pos = MAP_APPFONT ( 80 , 14 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 14 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 14 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Size = MAP_APPFONT ( 90 , 80 ) ;
         TabStop = TRUE ;
         DropDown = TRUE ;
@@ -100,13 +108,19 @@ TabPage TP_VALIDATION_VALUES
     FixedText FT_VALUE
     {
         Pos = MAP_APPFONT ( 6 , 58 ) ;
-        Size = MAP_APPFONT ( 70 , 8 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Size = MAP_APPFONT ( 70 , 8 ) ;
+        Size = MAP_APPFONT ( 70 - OFFSET_X , 8 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Text [ en-US ] = "~Data" ;
     };
     ListBox LB_VALUE
     {
         Border = TRUE ;
-        Pos = MAP_APPFONT ( 80 , 56 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 56 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 56 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Size = MAP_APPFONT ( 90 , 90 ) ;
         TabStop = TRUE ;
         DropDown = TRUE ;
@@ -126,54 +140,81 @@ TabPage TP_VALIDATION_VALUES
     FixedText FT_MIN
     {
         Pos = MAP_APPFONT ( 6 , 76 ) ;
-        Size = MAP_APPFONT ( 70 , 8 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Size = MAP_APPFONT ( 70 , 8 ) ;
+        Size = MAP_APPFONT ( 70 - OFFSET_X , 8 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Text [ en-US ] = "~Minimum" ;
     };
     Edit EDT_MIN
     {
         Border = TRUE ;
-        Pos = MAP_APPFONT ( 80 , 74 ) ;
-        Size = MAP_APPFONT ( 174 , 12 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 74 ) ;
+        //Size = MAP_APPFONT ( 174 , 12 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 74 ) ;
+        Size = MAP_APPFONT ( 90 , 12 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         TabStop = TRUE ;
     };
     MultiLineEdit EDT_LIST
     {
         Border = TRUE ;
-        Pos = MAP_APPFONT ( 80 , 74 ) ;
-        Size = MAP_APPFONT ( 174 , 105 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 74 ) ;
+        //Size = MAP_APPFONT ( 174 , 105 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 74 ) ;
+        Size = MAP_APPFONT ( 174 + OFFSET_X , 105 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         VScroll = TRUE ;
         IgnoreTab = TRUE ;
     };
     FixedText FT_MAX
     {
         Pos = MAP_APPFONT ( 6 , 92 ) ;
-        Size = MAP_APPFONT ( 70 , 8 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Size = MAP_APPFONT ( 70 , 8 ) ;
+        Size = MAP_APPFONT ( 70 - OFFSET_X , 8 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Text [ en-US ] = "Ma~ximum" ;
     };
     Edit EDT_MAX
     {
         Border = TRUE ;
-        Pos = MAP_APPFONT ( 80 , 90 ) ;
-        Size = MAP_APPFONT ( 174 , 12 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 90 ) ;
+        //Size = MAP_APPFONT ( 174 , 12 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 90 ) ;
+        Size = MAP_APPFONT ( 90 , 12 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         TabStop = TRUE ;
     };
     CheckBox TSB_ALLOW_BLANKS
     {
-        Pos = MAP_APPFONT ( 80 , 30 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 30 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 30 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Size = MAP_APPFONT ( 174 , 10 ) ;
         TabStop = TRUE ;
         Text [ en-US ] = "Allow ~blank cells" ;
     };
     CheckBox CB_SHOWLIST
     {
-        Pos = MAP_APPFONT ( 80 , 44 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 80 , 44 ) ;
+        Pos = MAP_APPFONT ( 80 - OFFSET_X , 44 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Size = MAP_APPFONT ( 174 , 10 ) ;
         TabStop = TRUE ;
         Text [ en-US ] = "Show selection ~list" ;
     };
     CheckBox CB_SORTLIST
     {
-        Pos = MAP_APPFONT ( 90 , 58 ) ;
+        //<!--Modified by PengYunQuan for Validity Cell Range Picker
+        //Pos = MAP_APPFONT ( 90 , 58 ) ;
+        Pos = MAP_APPFONT ( 90 - OFFSET_X , 58 ) ;
+        //-->Modified by PengYunQuan for Validity Cell Range Picker
         Size = MAP_APPFONT ( 164 , 10 ) ;
         TabStop = TRUE ;
         Text [ en-US ] = "Sor~t entries ascending" ;
@@ -185,6 +226,15 @@ TabPage TP_VALIDATION_VALUES
         WordBreak = TRUE ;
         Text [ en-US ] = "A valid source can only consist of a contiguous selection of rows and columns, or a formula that results in an area or array.";
     };
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    ImageButton RB_VALIDITY_REF
+    {
+        Pos = MAP_APPFONT ( 142 , 73 ) ;
+        Size = MAP_APPFONT ( 13 , 14 ) ;
+        TabStop = TRUE ;
+        QuickHelpText [ en-US ] = "Shrink" ;
+    };
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 };
 
 TabPage TP_VALIDATION_INPUTHELP
diff --git a/sc/source/ui/docshell/docsh.cxx b/sc/source/ui/docshell/docsh.cxx
index 4adf06b3bfd0..fd67755daf83 100644
--- a/sc/source/ui/docshell/docsh.cxx
+++ b/sc/source/ui/docshell/docsh.cxx
@@ -2580,3 +2580,16 @@ void ScDocShellModificator::SetDocumentModified()
         pDoc->BroadcastUno( SfxSimpleHint( SFX_HINT_DATACHANGED ) );
     }
 }
+
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+sal_Bool ScDocShell::AcceptStateUpdate() const
+{
+    if( SfxObjectShell::AcceptStateUpdate() )
+        return sal_True;
+
+    if( SC_MOD()->Find1RefWindow( SFX_APP()->GetTopWindow() ) )
+        return sal_True;
+
+    return sal_False;
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
diff --git a/sc/source/ui/inc/anyrefdg.hxx b/sc/source/ui/inc/anyrefdg.hxx
index 9e2d833b27d0..18125076d5cf 100644
--- a/sc/source/ui/inc/anyrefdg.hxx
+++ b/sc/source/ui/inc/anyrefdg.hxx
@@ -42,19 +42,28 @@
 #endif
 #include <sfx2/basedlgs.hxx>
 #include "address.hxx"
+#include "cell.hxx"
+#include "compiler.hxx"
 #include "formula/funcutl.hxx"
 #include "IAnyRefDialog.hxx"
+#include "scresid.hxx"
 #include <memory>
 
 class SfxObjectShell;
 class ScRange;
 class ScDocument;
 class ScTabViewShell;
-class ScAnyRefDlg;
-class ScFormulaCell;
-class ScCompiler;
+//The class of ScAnyRefDlg is rewritten by PengYunQuan for Validity Cell Range Picker
+//class ScAnyRefDlg;
+class ScRefHandler;
 class ScRangeList;
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+class SfxShell;
+#include "scmod.hxx"
 
+typedef    formula::RefButton   ScRefButton;
+typedef        formula::RefEdit ScRefEdit;
+//-->Added by PengYunQuan for Validity Cell Range Picker
 class ScFormulaReferenceHelper
 {
     IAnyRefDialog*      m_pDlg;
@@ -104,12 +113,28 @@ public:
     void                ViewShellChanged( ScTabViewShell* pScViewShell );
 
     static              void enableInput(BOOL _bInput);
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+protected:
+    Window      *       GetWindow(){ return m_pWindow; }
+public:
+    bool                CanInputStart( const ScRefEdit *pEdit ){ return !!pEdit; }
+    bool                CanInputDone( BOOL bForced ){   return pRefEdit && (bForced || !pRefBtn);   }
+//<!--Added by PengYunQuan for Validity Cell Range Picker
 };
 //============================================================================
 
-class ScAnyRefDlg : public SfxModelessDialog,
+//The class of ScAnyRefDlg is rewritten by PengYunQuan for Validity Cell Range Picker
+class SC_DLLPUBLIC ScRefHandler : //public SfxModelessDialog,
                     public IAnyRefDialog
 {
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+    Window &    m_rWindow;
+    bool        m_bInRefMode;
+public:
+    operator Window *(){ return &m_rWindow; }
+    Window  * operator ->() { return static_cast<Window *>(*this); }
+    template<class,bool> friend class ScRefHdlrImplBase;
+//-->Added by PengYunQuan for Validity Cell Range Picker
     friend class        formula::RefButton;
     friend class        formula::RefEdit;
 
@@ -130,7 +155,8 @@ protected:
 
     void                SetDispatcherLock( BOOL bLock );
 
-    virtual long        PreNotify( NotifyEvent& rNEvt );
+    //Overwrite TWindow will implemented by ScRefHdlrImplBase
+    //virtual long        PreNotify( NotifyEvent& rNEvt );
 
     virtual void        RefInputStart( formula::RefEdit* pEdit, formula::RefButton* pButton = NULL );
     virtual void        RefInputDone( BOOL bForced = FALSE );
@@ -140,9 +166,9 @@ protected:
     bool                ParseWithNames( ScRangeList& rRanges, const String& rStr, ScDocument* pDoc );
 
 public:
-                        ScAnyRefDlg( SfxBindings* pB, SfxChildWindow* pCW,
-                                     Window* pParent, USHORT nResId);
-    virtual             ~ScAnyRefDlg();
+                        ScRefHandler( Window &rWindow, SfxBindings* pB/*, SfxChildWindow* pCW,
+                                     Window* pParent, USHORT nResId*/, bool bBindRef );
+    virtual             ~ScRefHandler();
 
     virtual void        SetReference( const ScRange& rRef, ScDocument* pDoc ) = 0;
     virtual void        AddRefEntry();
@@ -159,15 +185,100 @@ public:
 
     virtual void        ViewShellChanged( ScTabViewShell* pScViewShell );
     void                SwitchToDocument();
-    SfxBindings&        GetBindings();
+    //SfxBindings&        GetBindings();
 
     virtual void        SetActive() = 0;
 //  virtual BOOL        Close();
+    //Overwrite TWindow will implemented by ScRefHdlrImplBase
+    //virtual void        StateChanged( StateChangedType nStateChange );
+
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+public:
+    bool                EnterRefMode();
+    bool                LeaveRefMode();
+    inline  bool        CanInputStart( const ScRefEdit *pEdit );
+    inline  bool        CanInputDone( BOOL bForced );
+//-->Added by PengYunQuan for Validity Cell Range Picker
+};
+
+
+//============================================================================
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+template<  class TWindow, bool bBindRef = true >
+class ScRefHdlrImplBase:public TWindow, public ScRefHandler
+{
+public:
+    //Overwrite TWindow
+    virtual long        PreNotify( NotifyEvent& rNEvt );
     virtual void        StateChanged( StateChangedType nStateChange );
+
+private:
+    template<class TBindings, class TChildWindow, class TParentWindow, class TResId>
+    ScRefHdlrImplBase( TBindings* pB, TChildWindow* pCW,
+        TParentWindow* pParent, TResId nResId);
+
+    template<class TParentWindow, class TResId, class TArg>
+    ScRefHdlrImplBase( TParentWindow* pParent, TResId nResId, const TArg &rArg, SfxBindings *pB = NULL );
+
+    ~ScRefHdlrImplBase();
+
+    template<class, class, bool> friend struct ScRefHdlrImpl;
 };
 
+template<class TWindow, bool bBindRef>
+template<class TBindings, class TChildWindow, class TParentWindow, class TResId>
+ScRefHdlrImplBase<TWindow, bBindRef>::ScRefHdlrImplBase( TBindings* pB, TChildWindow* pCW,
+                 TParentWindow* pParent, TResId nResId):TWindow(pB, pCW, pParent, ScResId(static_cast<USHORT>( nResId ) ) ), ScRefHandler( *static_cast<TWindow*>(this), pB, bBindRef ){}
+
+template<class TWindow, bool bBindRef >
+template<class TParentWindow, class TResId, class TArg>
+ScRefHdlrImplBase<TWindow,bBindRef>::ScRefHdlrImplBase( TParentWindow* pParent, TResId nResIdP, const TArg &rArg, SfxBindings *pB /*= NULL*/ )
+:TWindow( pParent, ScResId(static_cast<USHORT>( nResIdP )), rArg ), ScRefHandler( *static_cast<TWindow*>(this), pB, bBindRef ){}
+
+template<class TWindow, bool bBindRef >
+ScRefHdlrImplBase<TWindow,bBindRef>::~ScRefHdlrImplBase(){}
 
 //============================================================================
+template<class TDerived, class TBase, bool bBindRef = true>
+struct ScRefHdlrImpl: ScRefHdlrImplBase<TBase, bBindRef >
+{
+    enum { UNKNOWN_SLOTID = 0U, SLOTID = UNKNOWN_SLOTID };
+
+    template<class T1, class T2, class T3, class T4>
+    ScRefHdlrImpl( const T1 & rt1, const T2 & rt2, const T3 & rt3, const T4 & rt4 ):ScRefHdlrImplBase<TBase, bBindRef >(rt1, rt2, rt3, rt4 )
+    {
+        SC_MOD()->RegisterRefWindow( static_cast<USHORT>( static_cast<TDerived*>(this)->SLOTID ), this );
+    }
+
+    ~ScRefHdlrImpl()
+    {
+        SC_MOD()->UnregisterRefWindow( static_cast<USHORT>( static_cast<TDerived*>(this)->SLOTID ), this );
+    }
+};
+//============================================================================
+struct ScAnyRefDlg : ::ScRefHdlrImpl< ScAnyRefDlg, SfxModelessDialog>
+{
+    template<class T1, class T2, class T3, class T4>
+    ScAnyRefDlg( const T1 & rt1, const T2 & rt2, const T3 & rt3, const T4 & rt4 ):ScRefHdlrImpl< ScAnyRefDlg, SfxModelessDialog>(rt1, rt2, rt3, rt4){}
+};
+//============================================================================
+
+inline bool ScRefHandler::CanInputStart( const ScRefEdit *pEdit )
+{
+    return m_aHelper.CanInputStart( pEdit );
+}
+
+inline  bool ScRefHandler::CanInputDone( BOOL bForced )
+{
+    return m_aHelper.CanInputDone( bForced );
+}
+
+template <> SC_DLLPUBLIC void ScRefHdlrImplBase<SfxModelessDialog,true>::StateChanged( StateChangedType nStateChange );
+template <> SC_DLLPUBLIC long ScRefHdlrImplBase<SfxModelessDialog,true>::PreNotify( NotifyEvent& rNEvt );
+#include <sfx2/tabdlg.hxx>
+template <> SC_DLLPUBLIC void ScRefHdlrImplBase<SfxTabDialog,false>::StateChanged( StateChangedType nStateChange );
+template <> SC_DLLPUBLIC long ScRefHdlrImplBase<SfxTabDialog,false>::PreNotify( NotifyEvent& rNEvt );
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
 #endif // SC_ANYREFDG_HXX
 
diff --git a/sc/source/ui/inc/docsh.hxx b/sc/source/ui/inc/docsh.hxx
index 9556b4ad8138..548125bffd9a 100644
--- a/sc/source/ui/inc/docsh.hxx
+++ b/sc/source/ui/inc/docsh.hxx
@@ -418,7 +418,9 @@ public:
 
     const ScOptSolverSave* GetSolverSaveData() const    { return pSolverSaveData; }     // may be null
     void            SetSolverSaveData( const ScOptSolverSave& rData );
-
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    sal_Bool        AcceptStateUpdate() const;
+    //-->Added by PengYunQuan for Validity Cell Range Picker
     ScSheetSaveData* GetSheetSaveData();
 };
 
diff --git a/sc/source/ui/inc/reffact.hxx b/sc/source/ui/inc/reffact.hxx
index cbd5da901a63..bc43aedf918f 100644
--- a/sc/source/ui/inc/reffact.hxx
+++ b/sc/source/ui/inc/reffact.hxx
@@ -102,6 +102,22 @@ class ScSimpleRefDlgWrapper: public SfxChildWindow
         void            StartRefInput();
 };
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+class SC_DLLPUBLIC ScValidityRefChildWin : public SfxChildWindow
+{
+    bool    m_bVisibleLock:1;
+    bool    m_bFreeWindowLock:1;
+    Window * m_pSavedWndParent;
+public:
+    ScValidityRefChildWin( Window*, USHORT, SfxBindings*, SfxChildWinInfo* );
+    SFX_DECL_CHILDWINDOW(ScValidityRefChildWin);
+    ~ScValidityRefChildWin();
+    bool    LockVisible( bool bLock ){ bool bVis = m_bVisibleLock; m_bVisibleLock = bLock; return bVis; }
+    bool    LockFreeWindow( bool bLock ){ bool bFreeWindow = m_bFreeWindowLock; m_bFreeWindowLock = bLock; return bFreeWindow; }
+    void                Hide(){ if( !m_bVisibleLock) SfxChildWindow::Hide(); }
+    void                Show( USHORT nFlags ){ if( !m_bVisibleLock ) SfxChildWindow::Show( nFlags ); }
+};
+//-->Added by PengYunQuan for Validity Cell Range Picker
 
 //==================================================================
 
diff --git a/sc/source/ui/inc/tabview.hxx b/sc/source/ui/inc/tabview.hxx
index 9ec5ce507209..dab44cdf33df 100644
--- a/sc/source/ui/inc/tabview.hxx
+++ b/sc/source/ui/inc/tabview.hxx
@@ -390,7 +390,10 @@ public:
 
     void            SetNewStartIfMarking();
 
-    void            SetTabNo( SCTAB nTab, BOOL bNew = FALSE, BOOL bExtendSelection = FALSE );
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    //void            SetTabNo( SCTAB nTab, BOOL bNew = FALSE, BOOL bExtendSelection = FALSE );
+    SC_DLLPUBLIC void            SetTabNo( SCTAB nTab, BOOL bNew = FALSE, BOOL bExtendSelection = FALSE );
+    //-->Added by PengYunQuan for Validity Cell Range Picker
     void            SelectNextTab( short nDir, BOOL bExtendSelection = FALSE );
 
     void            ActivateView( BOOL bActivate, BOOL bFirst );
diff --git a/sc/source/ui/inc/validate.hrc b/sc/source/ui/inc/validate.hrc
index 0f69e6a55136..4d46d42263b2 100644
--- a/sc/source/ui/inc/validate.hrc
+++ b/sc/source/ui/inc/validate.hrc
@@ -61,6 +61,9 @@
 #define CB_SORTLIST             28
 #define EDT_LIST                29
 #define FT_SOURCEHINT           30
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+#define RB_VALIDITY_REF         88
+//-->Added by PengYunQuan for Validity Cell Range Picker
 
 /*  Position indexes for "Allow" list box.
     They do not map directly to ScValidationMode and can safely be modified to
diff --git a/sc/source/ui/inc/validate.hxx b/sc/source/ui/inc/validate.hxx
index 2844a9f74e0f..0ac51e75b5d5 100644
--- a/sc/source/ui/inc/validate.hxx
+++ b/sc/source/ui/inc/validate.hxx
@@ -36,23 +36,164 @@
 #include <vcl/fixed.hxx>
 #include <vcl/lstbox.hxx>
 #include <svtools/svmedit.hxx>
-
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+#include "anyrefdg.hxx"
+//-->Added by PengYunQuan for Validity Cell Range Picker
 
 // ============================================================================
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+struct  ScRefHandlerCaller{
+    virtual ~ScRefHandlerCaller(){}
+};
+class ScRefHandlerHelper
+{
+protected:
+    ScRefHandlerCaller  *m_pHandler;
+    void            (ScRefHandlerCaller::*m_pSetReferenceHdl)( const ScRange& , ScDocument* );
+    void            (ScRefHandlerCaller::*m_pSetActiveHdl)();
+    void            (ScRefHandlerCaller::*m_pRefInputStartPreHdl)( ScRefEdit* pEdit, ScRefButton* pButton );
+    void            (ScRefHandlerCaller::*m_pRefInputStartPostHdl)( ScRefEdit* pEdit, ScRefButton* pButton );
+    void            (ScRefHandlerCaller::*m_pRefInputDonePreHdl)();
+    void            (ScRefHandlerCaller::*m_pRefInputDonePostHdl)();
+
+public:
+    typedef void            (ScRefHandlerCaller::*PFUNCSETREFHDLTYPE)( const ScRange& , ScDocument* );
+    typedef void            (ScRefHandlerCaller::*PCOMMONHDLTYPE)();
+    typedef void            (ScRefHandlerCaller::*PINPUTSTARTDLTYPE)(  ScRefEdit* pEdit, ScRefButton* pButton );
+
+    PFUNCSETREFHDLTYPE  SetSetRefHdl(  PFUNCSETREFHDLTYPE pNewHdl )
+    {
+        PFUNCSETREFHDLTYPE pOldHdl = m_pSetReferenceHdl;
+        m_pSetReferenceHdl = pNewHdl;
+        return pOldHdl;
+    }
+
+    PCOMMONHDLTYPE  SetSetActHdl(  PCOMMONHDLTYPE pNewHdl )
+    {
+        PCOMMONHDLTYPE pOldHdl = m_pSetActiveHdl;
+        m_pSetActiveHdl = pNewHdl;
+        return pOldHdl;
+    }
+
+    ScRefHandlerCaller  *SetHandler( ScRefHandlerCaller *pNewHandler )
+    {
+        ScRefHandlerCaller  *pOldHandler = m_pHandler;
+        m_pHandler = pNewHandler;
+        return pOldHandler;
+    }
+    void    SetRefInputStartPreHdl( PINPUTSTARTDLTYPE pNewHdl   ){  m_pRefInputStartPreHdl = pNewHdl;   }
+    void    SetRefInputDonePostHdl( void            (ScRefHandlerCaller::*pNewHdl)()    ){  m_pRefInputDonePostHdl = pNewHdl;   }
+    void    SetRefInputStartPostHdl(    PINPUTSTARTDLTYPE pNewHdl   ){  m_pRefInputStartPostHdl = pNewHdl;  }
+    void    SetRefInputDonePreHdl( void            (ScRefHandlerCaller::*pNewHdl)() ){  m_pRefInputDonePreHdl = pNewHdl;    }
+
+    ScRefHandlerHelper():m_pHandler(NULL), m_pSetReferenceHdl( NULL ), m_pSetActiveHdl(NULL),  m_pRefInputStartPreHdl( NULL ), m_pRefInputStartPostHdl( NULL ), m_pRefInputDonePreHdl( NULL ),  m_pRefInputDonePostHdl( NULL ){}
+};
+//-->Added by PengYunQuan for Validity Cell Range Picker
+
 /** The "Validity" tab dialog. */
-class ScValidationDlg : public SfxTabDialog
+//<!--Modified by PengYunQuan for Validity Cell Range Picker
+//class ScValidationDlg : public SfxTabDialog
+class ScValidationDlg :public ScRefHdlrImpl<ScValidationDlg, SfxTabDialog, false>, public ScRefHandlerHelper
+//-->Modified by PengYunQuan for Validity Cell Range Picker
 {
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    typedef ScRefHdlrImpl<ScValidationDlg, SfxTabDialog, false> ScValidationDlgBase;
+
+    //Start_Moddify by liliang 03/26/2008 SODC_13677_2
+    DECL_LINK( OkHdl, Button * );
+    //End_Moddify by liliang 03/26/2008 SODC_13677_2
+    bool    m_bOwnRefHdlr:1;
+
+    ScTabViewShell *m_pTabVwSh;
+    bool    m_bRefInputting:1;
+    bool    EnterRefStatus();
+    bool    LeaveRefStatus();
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 public:
-    explicit                    ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet );
+    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+    //explicit                    ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet );
+    explicit ScValidationDlg( Window* pParent, const SfxItemSet* pArgSet, ScTabViewShell * pTabViewSh, SfxBindings *pB = NULL );
+    //-->Modified by PengYunQuan for Validity Cell Range Picker
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
     virtual                     ~ScValidationDlg();
+    inline static ScValidationDlg * Find1AliveObject( Window *pAncestor );
+    bool    IsAlive();
+    inline  ScTabViewShell * GetTabViewShell();
+
+    bool    SetupRefDlg();
+    bool    RemoveRefDlg( BOOL bRestoreModal = TRUE );
+
+    virtual void            SetModal( BOOL bModal ){ ScValidationDlgBase::SetModalInputMode( bModal ); }
+
+    virtual void            SetReference( const ScRange& rRef, ScDocument* pDoc )
+    {
+        if ( m_pHandler && m_pSetReferenceHdl )
+            (m_pHandler->*m_pSetReferenceHdl)( rRef, pDoc );
+    }
+
+    virtual void            SetActive()
+    {
+        if ( m_pHandler && m_pSetActiveHdl )
+            (m_pHandler->*m_pSetActiveHdl)();
+    }
+
+    void        CloseRefDialog()
+    {
+        DBG_ASSERT( false, "should not execute here!!!when the edit kill focus, should remove refhandler.\r\n" );
+
+        if ( IsInExecute() )
+            EndDialog( FALSE );
+        else if ( GetStyle() & WB_CLOSEABLE )
+            Close();
+    }
+
+    bool IsRefInputting(){  return m_bRefInputting; }
+
+    virtual void        RefInputStart( ScRefEdit* pEdit, ScRefButton* pButton = NULL )
+    {
+        if( !CanInputStart( pEdit ) )
+            return;
+
+        if ( m_pHandler && m_pRefInputStartPreHdl )
+            (m_pHandler->*m_pRefInputStartPreHdl)( pEdit, pButton );
+        m_bRefInputting = true;
+        ScValidationDlgBase::RefInputStart( pEdit, pButton );
+        if ( m_pHandler && m_pRefInputStartPostHdl )
+            (m_pHandler->*m_pRefInputStartPostHdl)( pEdit, pButton );
+    }
+
+    virtual void        RefInputDone( BOOL bForced = FALSE )
+    {
+        if( !CanInputDone( bForced ) )
+            return;
+
+        if ( m_pHandler && m_pRefInputDonePreHdl )
+            (m_pHandler->*m_pRefInputDonePreHdl)();
+
+        ScValidationDlgBase::RefInputDone( bForced );
+        m_bRefInputting = false;
+
+        if ( m_pHandler && m_pRefInputDonePostHdl )
+            (m_pHandler->*m_pRefInputDonePostHdl)();
+    }
+
+    BOOL IsChildFocus();
+
+    enum { SLOTID = SID_VALIDITY_REFERENCE };
+
+    BOOL Close();
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 };
 
 
 // ============================================================================
 
 /** The tab page "Criteria" from the Validation dialog. */
-class ScTPValidationValue : public SfxTabPage
+//<!--Modified by PengYunQuan for Validity Cell Range Picker
+//class ScTPValidationValue : public SfxTabPage
+class ScTPValidationValue : public ScRefHandlerCaller, public SfxTabPage
+//-->Modified by PengYunQuan for Validity Cell Range Picker
 {
 public:
     explicit                    ScTPValidationValue( Window* pParent, const SfxItemSet& rArgSet );
@@ -84,10 +225,16 @@ private:
     FixedText                   maFtValue;
     ListBox                     maLbValue;
     FixedText                   maFtMin;
-    Edit                        maEdMin;
+    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+    //Edit                        maEdMin;
+    ScRefEdit                        maEdMin;
+    //-->Modified by PengYunQuan for Validity Cell Range Picker
     MultiLineEdit               maEdList;       /// Entries for explicit list
     FixedText                   maFtMax;
-    Edit                        maEdMax;
+    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+    //Edit                        maEdMax;
+    ScRefEdit                        maEdMax;
+    //-->Modified by PengYunQuan for Validity Cell Range Picker
     FixedText                   maFtHint;       /// Hint text for cell range validity.
 
     String                      maStrMin;
@@ -96,6 +243,33 @@ private:
     String                      maStrRange;
     String                      maStrList;
     sal_Unicode                 mcFmlaSep;      /// List separator in formulas.
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    DECL_LINK( EditSetFocusHdl, Edit *);
+    DECL_LINK( KillFocusHdl, Window *);
+    void    OnClick( Button *pBtn );
+    ScRefEdit   *m_pRefEdit;
+    class ScRefButtonEx:public ::ScRefButton
+    {
+        void Click();
+    public:
+        ScRefButtonEx( Window* pParent, const ResId& rResId, ScRefEdit* pEdit = NULL, ScRefHandler *pRefHdlr = NULL ): ::ScRefButton( pParent, rResId, pEdit, pRefHdlr ){}
+    }m_btnRef;
+    friend class ScRefButtonEx;
+    void            SetReferenceHdl( const ScRange& , ScDocument* );
+    void            SetActiveHdl();
+    void            RefInputStartPreHdl( ScRefEdit* pEdit, ScRefButton* pButton );
+    void            RefInputDonePreHdl();
+    void            RefInputDonePostHdl();
+    ScValidationDlg * GetValidationDlg();
+
+    //TYPEINFO();
+    void            TidyListBoxes();
+public:
+    USHORT          GetAllowEntryPos();
+    String          GetMinText();
+    void    SetupRefDlg();
+    void    RemoveRefDlg();
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 };
 
 
@@ -161,6 +335,16 @@ public:
     virtual void        Reset       ( const SfxItemSet& rArgSet );
 };
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+inline ScTabViewShell *ScValidationDlg::GetTabViewShell()
+{
+    return m_pTabVwSh;
+}
 
+inline ScValidationDlg * ScValidationDlg::Find1AliveObject( Window *pAncestor )
+{
+    return static_cast<ScValidationDlg *>( SC_MOD()->Find1RefWindow( SLOTID, pAncestor ) );
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
 #endif // SC_VALIDATE_HXX
 
diff --git a/sc/source/ui/miscdlgs/anyrefdg.cxx b/sc/source/ui/miscdlgs/anyrefdg.cxx
index befabb51a17c..0d93eb0c7a93 100644
--- a/sc/source/ui/miscdlgs/anyrefdg.cxx
+++ b/sc/source/ui/miscdlgs/anyrefdg.cxx
@@ -377,7 +377,10 @@ IMPL_LINK( ScFormulaReferenceHelper, AccelSelectHdl, Accelerator *, pSelAccel )
 //----------------------------------------------------------------------------
 void ScFormulaReferenceHelper::RefInputDone( BOOL bForced )
 {
-    if (pRefEdit && (bForced || !pRefBtn))
+    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+    //if (pRefEdit && (bForced || !pRefBtn))
+    if ( CanInputDone( bForced ) )//if (pRefEdit && (bForced || !pRefBtn))
+    //-->Modified by PengYunQuan for Validity Cell Range Picker
     {
         if (bAccInserted)           // Accelerator wieder abschalten
         {
@@ -677,23 +680,33 @@ void lcl_HideAllReferences()
 }
 
 //============================================================================
-//  class ScAnyRefDlg
+//The class of ScAnyRefDlg is rewritten by PengYunQuan for Validity Cell Range Picker
+//  class ScRefHandler
 //----------------------------------------------------------------------------
 
-ScAnyRefDlg::ScAnyRefDlg( SfxBindings* pB, SfxChildWindow* pCW,
-                          Window* pParent, USHORT nResId)
-    :   SfxModelessDialog ( pB, pCW, pParent, ScResId( nResId ) ),
+ScRefHandler::ScRefHandler( Window &rWindow, SfxBindings* pB/*, SfxChildWindow* pCW,
+                          Window* pParent, USHORT nResId*/, bool bBindRef )
+    :   //SfxModelessDialog ( pB, pCW, pParent, ScResId( nResId ) ),
+        m_rWindow( rWindow ),
+        m_bInRefMode( false ),
         m_aHelper(this,pB),
         pMyBindings( pB ),
         pActiveWin(NULL)
 {
-    m_aHelper.SetWindow(this);
-    if(GetHelpId()==0)              //Hack, da im SfxModelessDialog die HelpId
-        SetHelpId(GetUniqueId());   //fuer einen ModelessDialog entfernt und
+    m_aHelper.SetWindow(/*this*/&m_rWindow);
+    if(m_rWindow.GetHelpId()==0)                //Hack, da im SfxModelessDialog die HelpId
+        m_rWindow.SetHelpId(m_rWindow.GetUniqueId());   //fuer einen ModelessDialog entfernt und
                                     //in eine UniqueId gewandelt wird, machen
                                     //wir das an dieser Stelle rueckgaengig.
     aTimer.SetTimeout(200);
-    aTimer.SetTimeoutHdl(LINK( this, ScAnyRefDlg, UpdateFocusHdl));
+    aTimer.SetTimeoutHdl(LINK( this, ScRefHandler, UpdateFocusHdl));
+
+    if( bBindRef ) EnterRefMode();
+}
+
+bool ScRefHandler::EnterRefMode()
+{
+    if( m_bInRefMode ) return false;
 
     SC_MOD()->InputEnterHandler();
 //    ScTabViewShell* pScViewShell = ScTabViewShell::GetActiveViewShell();
@@ -740,15 +753,25 @@ ScAnyRefDlg::ScAnyRefDlg( SfxBindings* pB, SfxChildWindow* pCW,
     m_aHelper.SetDispatcherLock( TRUE );
     //@Test
     //SFX_APPWINDOW->Disable(TRUE);   //@BugID 54702
+
+    return m_bInRefMode = true;
 }
 
 //----------------------------------------------------------------------------
 
-ScAnyRefDlg::~ScAnyRefDlg()
+ScRefHandler::~ScRefHandler()
+{
+    LeaveRefMode();
+}
+
+bool ScRefHandler::LeaveRefMode()
 {
+    if( !m_bInRefMode ) return false;
+
     lcl_HideAllReferences();
 
-    SetModalInputMode(FALSE);
+    if( Dialog *pDlg = dynamic_cast<Dialog*>( static_cast<Window*>(*this) ) )
+        pDlg->SetModalInputMode(FALSE);
     SetDispatcherLock( FALSE );         //! here and in DoClose ?
 
     ScTabViewShell* pScViewShell = ScTabViewShell::GetActiveViewShell();
@@ -757,20 +780,23 @@ ScAnyRefDlg::~ScAnyRefDlg()
 
     //SFX_APPWINDOW->Enable(TRUE,TRUE);
     lcl_InvalidateWindows();
+
+    m_bInRefMode = false;
+    return true;
 }
 
 //----------------------------------------------------------------------------
 
-SfxBindings& ScAnyRefDlg::GetBindings()
-{
-    //! SfxModelessDialog should allow access to pBindings pointer
-
-    return *pMyBindings;
-}
+//SfxBindings& ScRefHandler::GetBindings()
+//{
+//  //! SfxModelessDialog should allow access to pBindings pointer
+//
+//  return *pMyBindings;
+//}
 
 //----------------------------------------------------------------------------
 
-void ScAnyRefDlg::SwitchToDocument()
+void ScRefHandler::SwitchToDocument()
 {
     ScTabViewShell* pCurrent = ScTabViewShell::GetActiveViewShell();
     if (pCurrent)
@@ -800,7 +826,7 @@ void ScAnyRefDlg::SwitchToDocument()
 
 //----------------------------------------------------------------------------
 
-BOOL ScAnyRefDlg::IsDocAllowed(SfxObjectShell* pDocSh) const    // pDocSh may be 0
+BOOL ScRefHandler::IsDocAllowed(SfxObjectShell* pDocSh) const   // pDocSh may be 0
 {
     //  default: allow only same document (overridden in function dialog)
     String aCmpName;
@@ -813,41 +839,41 @@ BOOL ScAnyRefDlg::IsDocAllowed(SfxObjectShell* pDocSh) const    // pDocSh may be
 
 //----------------------------------------------------------------------------
 
-BOOL __EXPORT ScAnyRefDlg::IsRefInputMode() const
+BOOL __EXPORT ScRefHandler::IsRefInputMode() const
 {
-    return IsVisible(); // nur wer sichtbar ist kann auch Referenzen bekommen
+    return m_rWindow.IsVisible(); // nur wer sichtbar ist kann auch Referenzen bekommen
 }
 
 //----------------------------------------------------------------------------
 
-BOOL __EXPORT ScAnyRefDlg::DoClose( USHORT nId )
+BOOL __EXPORT ScRefHandler::DoClose( USHORT nId )
 {
     m_aHelper.DoClose(nId);
     return TRUE;
 }
 
-void ScAnyRefDlg::SetDispatcherLock( BOOL bLock )
+void ScRefHandler::SetDispatcherLock( BOOL bLock )
 {
     m_aHelper.SetDispatcherLock( bLock );
 }
 
 //----------------------------------------------------------------------------
 
-void ScAnyRefDlg::ViewShellChanged(ScTabViewShell*  pScViewShell )
+void ScRefHandler::ViewShellChanged(ScTabViewShell*  pScViewShell )
 {
     m_aHelper.ViewShellChanged(pScViewShell);
 }
 
 //----------------------------------------------------------------------------
 
-void ScAnyRefDlg::AddRefEntry()
+void ScRefHandler::AddRefEntry()
 {
     //  wenn nicht ueberladen, gibt es keine Mehrfach-Referenzen
 }
 
 //----------------------------------------------------------------------------
 
-BOOL __EXPORT ScAnyRefDlg::IsTableLocked() const
+BOOL __EXPORT ScRefHandler::IsTableLocked() const
 {
     // per Default kann bei Referenzeingabe auch die Tabelle umgeschaltet werden
 
@@ -861,48 +887,99 @@ BOOL __EXPORT ScAnyRefDlg::IsTableLocked() const
 //
 //----------------------------------------------------------------------------
 
-void ScAnyRefDlg::RefInputStart( formula::RefEdit* pEdit, formula::RefButton* pButton )
+void ScRefHandler::RefInputStart( formula::RefEdit* pEdit, formula::RefButton* pButton )
 {
     m_aHelper.RefInputStart( pEdit, pButton );
 }
 
 
-void ScAnyRefDlg::ToggleCollapsed( formula::RefEdit* pEdit, formula::RefButton* pButton )
+void ScRefHandler::ToggleCollapsed( formula::RefEdit* pEdit, formula::RefButton* pButton )
 {
     m_aHelper.ToggleCollapsed( pEdit, pButton );
 }
-long ScAnyRefDlg::PreNotify( NotifyEvent& rNEvt )
-{
-    USHORT nSwitch=rNEvt.GetType();
-    if(nSwitch==EVENT_GETFOCUS)
-    {
-        pActiveWin=rNEvt.GetWindow();
-    }
-    return SfxModelessDialog::PreNotify(rNEvt);
-}
-
-void ScAnyRefDlg::StateChanged( StateChangedType nStateChange )
-{
-    SfxModelessDialog::StateChanged( nStateChange );
 
-    if(nStateChange == STATE_CHANGE_VISIBLE)
-    {
-        if(IsVisible())
-        {
-            m_aHelper.enableInput( FALSE );
-            m_aHelper.EnableSpreadsheets();
-            m_aHelper.SetDispatcherLock( TRUE );
-            aTimer.Start();
-        }
-        else
-        {
-            m_aHelper.enableInput( TRUE );
-            m_aHelper.SetDispatcherLock( FALSE );           //! here and in DoClose ?
-        }
-    }
-}
-
-IMPL_LINK( ScAnyRefDlg, UpdateFocusHdl, Timer*, EMPTYARG )
+//The two following function is commentted out by PengYunQuan for Validity Cell Range Picker
+//long ScAnyRefDlg::PreNotify( NotifyEvent& rNEvt )
+//{
+//  USHORT nSwitch=rNEvt.GetType();
+//  if(nSwitch==EVENT_GETFOCUS)
+//  {
+//      pActiveWin=rNEvt.GetWindow();
+//  }
+//  return SfxModelessDialog::PreNotify(rNEvt);
+//}
+//
+//void ScAnyRefDlg::StateChanged( StateChangedType nStateChange )
+//{
+//  SfxModelessDialog::StateChanged( nStateChange );
+//
+//  if(nStateChange == STATE_CHANGE_VISIBLE)
+//  {
+//      if(IsVisible())
+//      {
+//          m_aHelper.enableInput( FALSE );
+//          m_aHelper.EnableSpreadsheets();
+//          m_aHelper.SetDispatcherLock( TRUE );
+//          aTimer.Start();
+//      }
+//      else
+//      {
+//          m_aHelper.enableInput( TRUE );
+//          m_aHelper.SetDispatcherLock( FALSE );           //! here and in DoClose ?
+//      }
+//  }
+//}
+
+#if defined( _MSC_VER )
+#define INTRODUCE_TEMPLATE
+#else
+#define INTRODUCE_TEMPLATE  template <>
+#endif
+
+#define IMPL_TWINDOW_PRENOTIFY( TWindow,bBindRef )  \
+INTRODUCE_TEMPLATE long ScRefHdlrImplBase<TWindow,bBindRef>::PreNotify( NotifyEvent& rNEvt )\
+{\
+    if( bBindRef || m_bInRefMode )\
+    {\
+        USHORT nSwitch=rNEvt.GetType();\
+        if(nSwitch==EVENT_GETFOCUS)\
+        {\
+            pActiveWin=rNEvt.GetWindow();\
+        }\
+    }\
+    return TWindow::PreNotify(rNEvt);\
+}
+
+#define IMPL_TWINDOW_STATECHANGED( TWindow,bBindRef )   \
+INTRODUCE_TEMPLATE void ScRefHdlrImplBase<TWindow,bBindRef>::StateChanged( StateChangedType nStateChange )\
+{\
+    TWindow::StateChanged( nStateChange );\
+\
+    if( !bBindRef && !m_bInRefMode ) return;\
+    \
+    if(nStateChange == STATE_CHANGE_VISIBLE)\
+    {\
+        if(m_rWindow.IsVisible())\
+        {\
+            m_aHelper.enableInput( FALSE );\
+            m_aHelper.EnableSpreadsheets();\
+            m_aHelper.SetDispatcherLock( TRUE );\
+            aTimer.Start();\
+        }\
+        else\
+        {\
+            m_aHelper.enableInput( TRUE );\
+            m_aHelper.SetDispatcherLock( FALSE );           /*//! here and in DoClose ?*/\
+        }\
+    }\
+}
+
+IMPL_TWINDOW_PRENOTIFY( SfxModelessDialog, true )
+IMPL_TWINDOW_PRENOTIFY( SfxTabDialog, false )
+IMPL_TWINDOW_STATECHANGED( SfxModelessDialog, true )
+IMPL_TWINDOW_STATECHANGED( SfxTabDialog, false )
+
+IMPL_LINK( ScRefHandler, UpdateFocusHdl, Timer*, EMPTYARG )
 {
     if (pActiveWin)
     {
@@ -911,27 +988,27 @@ IMPL_LINK( ScAnyRefDlg, UpdateFocusHdl, Timer*, EMPTYARG )
     return 0;
 }
 // -----------------------------------------------------------------------------
-bool ScAnyRefDlg::ParseWithNames( ScRangeList& rRanges, const String& rStr, ScDocument* pDoc )
+bool ScRefHandler::ParseWithNames( ScRangeList& rRanges, const String& rStr, ScDocument* pDoc )
 {
     return m_aHelper.ParseWithNames( rRanges, rStr, pDoc );
 }
 // -----------------------------------------------------------------------------
-void ScAnyRefDlg::HideReference( BOOL bDoneRefMode )
+void ScRefHandler::HideReference( BOOL bDoneRefMode )
 {
     m_aHelper.HideReference( bDoneRefMode );
 }
 // -----------------------------------------------------------------------------
-void ScAnyRefDlg::ShowReference( const XubString& rStr )
+void ScRefHandler::ShowReference( const XubString& rStr )
 {
     m_aHelper.ShowReference( rStr );
 }
 // -----------------------------------------------------------------------------
-void ScAnyRefDlg::ReleaseFocus( formula::RefEdit* pEdit, formula::RefButton* pButton )
+void ScRefHandler::ReleaseFocus( formula::RefEdit* pEdit, formula::RefButton* pButton )
 {
     m_aHelper.ReleaseFocus( pEdit,pButton );
 }
 //----------------------------------------------------------------------------
-void ScAnyRefDlg::RefInputDone( BOOL bForced )
+void ScRefHandler::RefInputDone( BOOL bForced )
 {
     m_aHelper.RefInputDone( bForced );
 }
diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx
index 039e4dc256ba..29db7f2286b3 100644
--- a/sc/source/ui/view/cellsh2.cxx
+++ b/sc/source/ui/view/cellsh2.cxx
@@ -209,6 +209,18 @@ BOOL lcl_GetSortParam( const ScViewData* pData, ScSortParam& rSortParam )
     return bSort;
 }
 
+//<!-- Added by PengYunQuan for Validity Cell Range Picker
+//after end execute from !IsModalInputMode, it is safer to delay deleting
+namespace
+{
+    long DelayDeleteAbstractDialog( void *pAbstractDialog, void * /*pArg*/ )
+    {
+        delete reinterpret_cast<VclAbstractDialog*>( pAbstractDialog );
+        return 0;
+    }
+}
+//--> Added by PengYunQuan for Validity Cell Range Picker
+
 void ScCellShell::ExecuteDB( SfxRequest& rReq )
 {
     ScTabViewShell* pTabViewShell   = GetViewData()->GetViewShell();
@@ -1060,10 +1072,18 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                     //CHINA001 ScAbstractDialogFactory* pFact = ScAbstractDialogFactory::Create();
                     //CHINA001 DBG_ASSERT(pFact, "ScAbstractFactory create fail!");//CHINA001
 
-                    SfxAbstractTabDialog* pDlg = pFact->CreateScValidationDlg( NULL, &aArgSet, TAB_DLG_VALIDATION );
+                    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+                    //SfxAbstractTabDialog* pDlg = pFact->CreateScValidationDlg( NULL, &aArgSet, TAB_DLG_VALIDATION );
+                    SfxAbstractTabDialog* pDlg = pFact->CreateScValidationDlg( NULL, &aArgSet, TAB_DLG_VALIDATION, pTabViewShell );
+                    //-->Modified by PengYunQuan for Validity Cell Range Picker
                     DBG_ASSERT(pDlg, "Dialog create fail!");//CHINA001
 
-                    if ( pDlg->Execute() == RET_OK )
+                    //<!--Modified by PengYunQuan for Validity Cell Range Picker
+                    //if ( pDlg->Execute() == RET_OK )
+                    short nResult = pDlg->Execute();
+                    pTabViewShell->SetTabNo( nTab );//When picking Cell Range ,other Tab may be switched. Need restore the correct tab
+                    if ( nResult == RET_OK )
+                    //-->Modified by PengYunQuan for Validity Cell Range Picker
                     {
                         const SfxItemSet* pOutSet = pDlg->GetOutputItemSet();
 
@@ -1142,7 +1162,11 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
                         pTabViewShell->SetValidation( aData );
                         rReq.Done( *pOutSet );
                     }
-                    delete pDlg;
+                    //<!-- Modified by PengYunQuan for Validity Cell Range Picker
+                    //after end execute from !IsModalInputMode, it is safer to delay deleting
+                    //delete pDlg;
+                    Application::PostUserEvent( Link( pDlg, &DelayDeleteAbstractDialog ) );
+                    //--> Modified by PengYunQuan for Validity Cell Range Picker
                 }
             }
             break;
diff --git a/sc/source/ui/view/makefile.mk b/sc/source/ui/view/makefile.mk
index 466ab5dc8627..d8f45c8a754f 100644
--- a/sc/source/ui/view/makefile.mk
+++ b/sc/source/ui/view/makefile.mk
@@ -161,7 +161,8 @@ EXCEPTIONSFILES= \
     $(SLO)$/tabvwshb.obj \
     $(SLO)$/viewdata.obj \
     $(SLO)$/viewfun5.obj \
-    $(SLO)$/viewfun7.obj
+    $(SLO)$/viewfun7.obj \
+    $(SLO)$/reffact.obj
 
 # goal seek -O2
 
diff --git a/sc/source/ui/view/reffact.cxx b/sc/source/ui/view/reffact.cxx
index 66f9cb88fb2c..8b36ae6a480e 100644
--- a/sc/source/ui/view/reffact.cxx
+++ b/sc/source/ui/view/reffact.cxx
@@ -47,6 +47,9 @@
 #include "acredlin.hxx"
 #include "simpref.hxx"
 #include "scmod.hxx"
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+#include "validate.hxx"
+//<!--Added by PengYunQuan for Validity Cell Range Picker
 
 // -----------------------------------------------------------------------
 
@@ -71,6 +74,27 @@ SFX_IMPL_CHILDWINDOW(ScFunctionDlgWrapper, SID_OPENDLG_FUNCTION )
 SFX_IMPL_CHILDWINDOW(ScEditFunctionDlgWrapper, SID_OPENDLG_EDITFUNCTION )
 SFX_IMPL_CHILDWINDOW(ScArgumentDlgWrapper, SID_OPENDLG_ARGUMENT )
 */
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+//SFX_IMPL_MODELESSDIALOG(ScValidityRefChildWin, SID_VALIDITY_REFERENCE )
+SFX_IMPL_CHILDWINDOW(ScValidityRefChildWin, SID_VALIDITY_REFERENCE)
+SfxChildWinInfo __EXPORT ScValidityRefChildWin::GetInfo() const
+{
+    SfxChildWinInfo anInfo = SfxChildWindow::GetInfo();
+
+    if( Window *pWnd = GetWindow() )
+    {
+        anInfo.aSize  = pWnd->GetSizePixel();
+
+        if( pWnd->IsDialog() )
+            if ( static_cast<Dialog*>(pWnd)->IsRollUp() )
+                anInfo.nFlags |= SFX_CHILDWIN_ZOOMIN;
+    }
+
+    return anInfo;
+}
+
+namespace { ScTabViewShell * lcl_GetTabViewShell( SfxBindings *pBindings ); }
+//<!--Added by PengYunQuan for Validity Cell Range Picker
 
 #define IMPL_CHILD_CTOR(Class,sid) \
     Class::Class( Window*               pParentP,                   \
@@ -79,8 +103,17 @@ SFX_IMPL_CHILDWINDOW(ScArgumentDlgWrapper, SID_OPENDLG_ARGUMENT )
                     SfxChildWinInfo*    pInfo )                     \
         : SfxChildWindow(pParentP, nId)                             \
     {                                                               \
-        ScTabViewShell* pViewShell =                                \
-            PTR_CAST( ScTabViewShell, SfxViewShell::Current() );    \
+        /*//<!--Added by PengYunQuan for Validity Cell Range Picker*/\
+        /************************************************************************************/\
+        /*      When a new document is creating, the SfxViewFrame may be ready,             */\
+        /*      But the ScTabViewShell may have not been activated yet. In this             */\
+        /*      situation, SfxViewShell::Current() does not get the correct shell,          */\
+        /*      and we should lcl_GetTabViewShell( p ) instead of SfxViewShell::Current()   */\
+        /************************************************************************************/\
+        ScTabViewShell* pViewShell = lcl_GetTabViewShell( p );      \
+        /*//-->Added by PengYunQuan for Validity Cell Range Picker*/\
+        if (!pViewShell)                                            \
+            pViewShell = PTR_CAST( ScTabViewShell, SfxViewShell::Current() ); \
         DBG_ASSERT( pViewShell, "missing view shell :-(" );         \
         pWindow = pViewShell ?                                      \
             pViewShell->CreateRefDialog( p, this, pInfo, pParentP, sid ) : NULL;    \
@@ -355,3 +388,50 @@ IMPL_CHILD_CTOR( ScHighlightChgDlgWrapper, FID_CHG_SHOW )
 /*------------------------------------------------------------------------*/
 
 
+//<!--Added by PengYunQuan for Validity Cell Range Picker
+namespace
+{
+    ScTabViewShell * lcl_GetTabViewShell( SfxBindings *pBindings )
+    {
+        if( pBindings )
+            if( SfxDispatcher* pDisp = pBindings ->GetDispatcher() )
+                if( SfxViewFrame *pFrm = pDisp->GetFrame() )
+                    if( SfxViewShell* pViewSh = pFrm->GetViewShell() )
+                        return dynamic_cast<ScTabViewShell*>( pViewSh );
+
+        return NULL;
+    }
+}
+
+ScValidityRefChildWin::ScValidityRefChildWin( Window*               pParentP,                   \
+                                             USHORT             nId,                        \
+                                             SfxBindings*       p,                          \
+                                             SfxChildWinInfo*   /*pInfo*/ )                     \
+                                             : SfxChildWindow(pParentP, nId),
+                                             m_bVisibleLock( false ),
+                                             m_bFreeWindowLock( false ),
+                                             m_pSavedWndParent( NULL )
+{
+    SetWantsFocus( FALSE );\
+        ScTabViewShell* pViewShell =                                \
+            NULL != ( pWindow =  ScValidationDlg::Find1AliveObject( pParentP ) ) ? static_cast<ScValidationDlg*>(pWindow)->GetTabViewShell() :
+            lcl_GetTabViewShell( p );
+        if (!pViewShell)
+            pViewShell = PTR_CAST( ScTabViewShell, SfxViewShell::Current() );
+        DBG_ASSERT( pViewShell, "missing view shell :-(" );         \
+        if (pViewShell && !pWindow)                                             \
+            pViewShell->GetViewFrame()->SetChildWindow( nId, FALSE );           \
+        else if( pWindow /*&& pWindow->ISA(ScValidationDlg)*/ )
+        {}//pWindow = new Window( pParentP, WB_HIDE );
+
+    if( pWindow ) m_pSavedWndParent = pWindow->GetParent();
+}
+
+ScValidityRefChildWin::~ScValidityRefChildWin()
+{
+    if( pWindow ) pWindow->SetParent( m_pSavedWndParent );
+
+    if( m_bFreeWindowLock )
+        pWindow = NULL;
+}
+//-->Added by PengYunQuan for Validity Cell Range Picker
diff --git a/sc/source/ui/view/tabvwsh.cxx b/sc/source/ui/view/tabvwsh.cxx
index 497eb65fc76f..4c73d14d51be 100644
--- a/sc/source/ui/view/tabvwsh.cxx
+++ b/sc/source/ui/view/tabvwsh.cxx
@@ -96,6 +96,10 @@ SFX_IMPL_INTERFACE(ScTabViewShell,SfxViewShell,ScResId(SCSTR_TABVIEWSHELL))
     SFX_CHILDWINDOW_REGISTRATION(GalleryChildWindow::GetChildWindowId());
     SFX_CHILDWINDOW_REGISTRATION(ScSpellDialogChildWindow::GetChildWindowId());
     SFX_CHILDWINDOW_REGISTRATION( ::avmedia::MediaPlayer::GetChildWindowId() );
+
+    //<!--Added by PengYunQuan for Validity Cell Range Picker
+    SFX_CHILDWINDOW_REGISTRATION(ScValidityRefChildWin::GetChildWindowId());
+    //-->Added by PengYunQuan for Validity Cell Range Picker
 }
 
 SFX_IMPL_VIEWFACTORY( ScTabViewShell, ScResId(STR_NONAME) )