 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Automatically display sentence.
Sir, First I want to thank Mr Max who responded me and tried to solve my problem. I m also very sorry that I have not good knowledge of English. If someone can understand my problem and can solve this I will be very grateful. I have a worksheet which have columns in this manner – A B C D Concession Form Received 6 List of C or D Form Received. 7 Form Form No. Date Amount 8 C Form 9 D Form 10 C Form 11 C Form 12 D Form 13 D Form 14 Full Tax 15 C Form I want to do that a sentence “List of C Form Received” or List of C Form Due”, or List of [ blank ] Form” automatically display in a6 in A6. only in that case when in b8 in front of Form; Form No is Filled. Otherwise it should be returned blank. This A6 content sentence should be change with Auto Filter filtering with specific C or D Form selection. Like when I select blank and C Form it should be “List of C Form not Recd”, when I selct not blank and C Form it should be display “List of C Form Recd.” Regards RRS Perhaps you might want to try this UDF from a previous post by Tom Ogilvy in microsoft.public.excel.programming [Subject: Re: Read AutoFilter Criteria] UDF = User defined function Tom's UDF will display the autofilter criteria selected in a cell A revised sample with Tom Ogilvy's UDF implemented is available at: http://www.savefile.com/files/4473648 Display_AutoComposed_Sentence_V2_Rao_newusers.xls To implement the UDF: Press Alt+F11 to go to VBE Click Insert > Module Copy and paste the UDF* into the white space on the right *everything within the dotted lines ("begin vba" to "end vba") Alt+Q to get back to Excel Then in Excel, we could use Tom's UDF by putting in a cell, say B3: =showfilter(A:A) B3 will return the filter criteria effected in col A If you choose "C Form" from the autofilter droplist in A7, B3 will return: "=C Form" Since we want to auto-compose the sentence in A6 by capture the autofilter criteria effected in cols A and B, we could try in A6 something like: ="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&" "&SUBSTITUTE(showfilter(B:B),"=",""))&" Received." You'd need to tinker around with all the possible autofilter criteria selected in A7 and B7 in your *actual* file, and see the returns from Tom's UDF. Then refine the formula in A6 further, possibly by using more nested SUBSTITUTE(...) so that each combo-selection will give the required "sentence" in A6. -- begin vba -- Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- end vba -- Hope this takes you a little closer to your goal .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > Sir, > First I want to thank Mr Max who responded me and tried to solve my problem. [quoted text clipped - 18 lines] > I want to do that a sentence "List of C Form Received" or List of C Form > Due", or List of [ blank ] Form" automatically display in a6 in A6. only in > that case when in b8 in front of Form; Form No is Filled. > [quoted text clipped - 3 lines] > specific C or D Form selection. Like when I select blank and C Form it should > be "List of C Form not Recd", when I selct not blank and C Form it should be > display "List of C Form Recd." > > Regards > RRS Perhaps you might want to try this UDF from a previous post by Tom Ogilvy in microsoft.public.excel.programming [Subject: Re: Read AutoFilter Criteria] UDF = User defined function Tom's UDF will display the autofilter criteria selected in a cell A revised sample with Tom Ogilvy's UDF implemented is available at: http://www.savefile.com/files/4473648 Display_AutoComposed_Sentence_V2_Rao_newusers.xls To implement the UDF: Press Alt+F11 to go to VBE Click Insert > Module Copy and paste the UDF* into the white space on the right *everything within the dotted lines ("begin vba" to "end vba") Alt+Q to get back to Excel Then in Excel, we could use Tom's UDF by putting in a cell, say B3: =showfilter(A:A) B3 will return the filter criteria effected in col A If you choose "C Form" from the autofilter droplist in A7, B3 will return: "=C Form" Since we want to auto-compose the sentence in A6 by capture the autofilter criteria effected in cols A and B, we could try in A6 something like: ="List of "&TRIM(SUBSTITUTE(showfilter(A:A),"=","")&" "&SUBSTITUTE(showfilter(B:B),"=",""))&" Received." You'd need to tinker around with all the possible autofilter criteria selected in A7 and B7 in your *actual* file, and see the returns from Tom's UDF. Then refine the formula in A6 further, possibly by using more nested SUBSTITUTE(...) so that each combo-selection will give the required "sentence" in A6. -- begin vba -- Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- end vba -- Hope this takes you a little closer to your goal .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > Sir, > First I want to thank Mr Max who responded me and tried to solve my problem. [quoted text clipped - 18 lines] > I want to do that a sentence "List of C Form Received" or List of C Form > Due", or List of [ blank ] Form" automatically display in a6 in A6. only in > that case when in b8 in front of Form; Form No is Filled. > [quoted text clipped - 3 lines] > specific C or D Form selection. Like when I select blank and C Form it should > be "List of C Form not Recd", when I selct not blank and C Form it should be > display "List of C Form Recd." > > Regards > RRS |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.