PDA

View Full Version : Solved: How To AutoFilter Method On A Spreadsheet Component



Erdin? E. Ka
10-22-2006, 11:14 AM
Hi everyone, :hi:

I am trying to filtering A1:C10 cells for "A" column, criteria is: "Demir".

But, doing nothing while i clicking to CommandButton1. :dunno



Private Sub CommandButton1_Click()
Dim FilteringCriteria As String
Dim ColumnNumber As Byte

FilteringCriteria = "Demir"
ColumnNumber = 1

On Error Resume Next

Spreadsheet1.ActiveSheet.ShowAllData

Spreadsheet1.ActiveSheet.Range("A1:C10").AutoFilter Field:=ColumnNumber, _
Criteria1:=FilteringCriteria

End Sub

Where|what is my mistake? Or what shuold i do?

Simon Lloyd
10-22-2006, 11:35 AM
Hi for filtering i use this
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Demir"
Regards,
Simon

Erdin? E. Ka
10-22-2006, 11:36 AM
Hi Simon,

Thank you for help. But i mean not on a Sheet. Just an ActiveX Spreadsheet Component on an UserForm.

Simon Lloyd
10-22-2006, 01:54 PM
Erdinc, i pasted your code in to a workbook trimmed some things and it worked, but im no expert by any means, this may not be what you are looking for!

Sub CommandButton1_Click()
Dim FilteringCriteria As String
Dim ColumnNumber As Byte

FilteringCriteria = "Demir"
ColumnNumber = 1
ActiveSheet.Range("A1:C10").AutoFilter Field:=ColumnNumber, _
Criteria1:=FilteringCriteria
End Sub
Regards,
Simon

Erdin? E. Ka
10-22-2006, 03:22 PM
Hi again.
Thank you Simon.
But i think that i couldn't tell well.
I have an UserForm1.
On this UserForm1; i have a Microsoft Office Spreadsheet 11.0 Additional Control object. I want to filtering on this Spreadsheet. Not on a worksheet.

Thank you to kindly help.

Erdin? E. Ka
10-22-2006, 03:31 PM
I am added the file. :hi:

malik641
10-23-2006, 03:07 PM
Hey Erdinc

I have to leave work, but I feel the battle is half-over.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocproFilters_HV05259737.asp

Take a look at that address. This does the opposite of what you want...but I feel like it's a lead to where you want to go with this :thumb

Hope this helps!

Private Sub CommandButton1_Click()
Dim FilteringCriteria As String
Dim afFilters
Dim afCol1
Dim afCol3
FilteringCriteria = "Demir"
On Error Resume Next
Spreadsheet1.Worksheets("Sheet1").ShowAllData

' Turn on AutoFilter.
Spreadsheet1.Worksheets("Sheet1").Range("A1:C10").AutoFilter
' Set a variable to the AutoFilter object.
Set afFilters = Spreadsheet1.Worksheets("Sheet1").AutoFilter
Set afCol1 = afFilters.Filters(1)
' Add a criteria that excludes "Demir" from column A.
afCol1.Criteria.Add FilteringCriteria
afFilters.Apply
End Sub

malik641
10-23-2006, 05:18 PM
Aha!!! Success!

Option Explicit
Private Sub CommandButton1_Click()
On Error Resume Next

Dim UniqueList As Object
Dim rngCriteria As Variant, cell As Variant
Dim i As Long
Dim ScriptDict As Variant

Dim FilteringCriteria As String
Dim afFilters
Dim afCol1

Set UniqueList = CreateObject("Scripting.Dictionary")

FilteringCriteria = "Demir"

Set rngCriteria = Spreadsheet1.Range("A2:A10")

For Each cell In rngCriteria
If cell.Text <> FilteringCriteria Then
UniqueList.Add cell.Text, cell.Text
End If
Next

Spreadsheet1.Worksheets("Sheet1").ShowAllData

'Turn on AutoFilter.
Spreadsheet1.Worksheets("Sheet1").Range("A1:C10").AutoFilter

'Set a variable to the AutoFilter object.
Set afFilters = Spreadsheet1.Worksheets("Sheet1").AutoFilter

'Specifies the first column in the range the autofilter was applied
Set afCol1 = afFilters.Filters(1)

'Add a criteria that excludes "Demir" from column A.
ScriptDict = UniqueList.Items

For i = 0 To UniqueList.Count - 1
afCol1.Criteria.Add ScriptDict(i)
Next

afFilters.Apply
End Sub

Here's some useful info I found on why the method you tried wasn't working:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocmthAutoFilter_HV05259703.asp

I feel that there may be a better way to do this....but I don't know (yet) :)

Hope this is what you were looking for :thumb :thumb


By the way, I haven't seen a worksheet on a userform before, it's SO cool!
I'm also wondering, what are you using it for?

Erdin? E. Ka
10-23-2006, 08:54 PM
Hey ErdincI have to leave work, but I feel the battle is half-over.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocproFilters_HV05259737.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocproFilters_HV05259737.asp)Take a look at that address. This does the opposite of what you want...but I feel like it's a lead to where you want to go with thisHope this helps!
Private Sub CommandButton1_Click()
Dim FilteringCriteria As String
Dim afFiltersDim afCol1
Dim afCol3FilteringCriteria = "Demir"

On Error Resume Next

Spreadsheet1.Worksheets("Sheet1").ShowAllData 'Turn on
AutoFilter.Spreadsheet1.Worksheets("Sheet1").Range("A1:C10").AutoFilter
'Set a variable to the AutoFilter object.
Set afFilters = Spreadsheet1.Worksheets("Sheet1").AutoFilter
Set afCol1 = afFilters.Filters(1)
'Add a criteria that excludes "Demir" from column A.
afCol1.Criteria.Add FilteringCriteriaafFilters.Apply
End SubHi Malik641,Actually, at the first time, i visited this link and and i prepared the codes below:But as you said, it was an opposite solution and there can be some truoble in large criterias.
Private Sub CommandButton1_Click()
Dim Suzme As Variant
Dim Sutun_1 As Variant
Dim Sutun_2 As Variant
Dim Kosul_1 As String
Dim Kosul_2 As String
Dim SutunNo As Byte
Kosul_1 = "Tuğla"
Kosul_2 = "?imento"
SutunNo = 1
Spreadsheet1.Worksheets("Sheet1").Range("A1:C10").AutoFilter
Set Suzme = Spreadsheet1.Worksheets("Sheet1").AutoFilter
Set Sutun_1 = Suzme.Filters(SutunNo)
Set Sutun_2 = Suzme.Filters(SutunNo)
Sutun_1.Criteria.Add Kosul_1
Sutun_2.Criteria.Add Kosul_2
Suzme.Apply
End Sub
__________________________________


Aha!!! Success!

Option Explicit
Private Sub CommandButton1_Click()
On Error Resume Next

Dim UniqueList As Object
Dim rngCriteria As Variant, cell As Variant
Dim i As Long
Dim ScriptDict As Variant

Dim FilteringCriteria As String
Dim afFilters
Dim afCol1

Set UniqueList = CreateObject("Scripting.Dictionary")

FilteringCriteria = "Demir"

Set rngCriteria = Spreadsheet1.Range("A2:A10")

For Each cell In rngCriteria
If cell.Text <> FilteringCriteria Then
UniqueList.Add cell.Text, cell.Text
End If
Next

Spreadsheet1.Worksheets("Sheet1").ShowAllData

'Turn on AutoFilter.
Spreadsheet1.Worksheets("Sheet1").Range("A1:C10").AutoFilter

'Set a variable to the AutoFilter object.
Set afFilters = Spreadsheet1.Worksheets("Sheet1").AutoFilter

'Specifies the first column in the range the autofilter was applied
Set afCol1 = afFilters.Filters(1)

'Add a criteria that excludes "Demir" from column A.
ScriptDict = UniqueList.Items

For i = 0 To UniqueList.Count - 1
afCol1.Criteria.Add ScriptDict(i)
Next

afFilters.Apply
End Sub
Here's some useful info I found on why the method you tried wasn't working:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocmthAutoFilter_HV05259703.asp

I feel that there may be a better way to do this....but I don't know (yet) :)

Hope this is what you were looking for :thumb :thumb


By the way, I haven't seen a worksheet on a userform before, it's SO cool!
I'm also wondering, what are you using it for?
Your second solution is really amazing. :thumb :p :yes
That's it!!. Yes, this is better and the best way to solve it.




By the way, I haven't seen a worksheet on a userform before, it's SO cool!
I'm also wondering, what are you using it for?
Actually i do not like much Spreadsheet component, becasue it is absent from a Worksheet...
In fact, i will not use this time for myselft. But on T?rkiye Board, my friend Sezar was asked us to how to solve it... Then i was researched and i wrote the absent code and then i was wondering how to writing best solution...

I was believe to i will find the best way from VBAX. :yes Now i am so happy.

Thank you very much. :friends:

malik641
10-23-2006, 09:14 PM
No problem Erdin? :thumb

I'm glad to see such faith in VBAX :)


I also want to comment that you've brought some really interesting questions to this site, and thanks for that :friends: it's a wealth of great info to absorb.


Actually i do not like much Spreadsheet component, becasue it is absent from a Worksheet...
In fact, i will not use this time for myselft. But on T?rkiye Board, my friend Sezar was asked us to how to solve it... Then i was researched and i wrote the absent code and then i was wondering how to writing best solution...
I see. Yeah I noticed that I couldn't set a range to a range variable...which was weird. I don't understand the "holes" that the Spreadsheet component has with an actual excel worksheet.

Erdin? E. Ka
10-23-2006, 09:29 PM
I am so happy if my question was useful for VBAX. :cloud9:

My opinion, Spreadsheet component must be developed more more more... :think: Not enaugh to using like this properties.

Now i will editing ( translating the variables of codes to T?rk?e ) your perfect solution for my friend Sezar.

Thank you very much again .. Malik641 to kindly help. :hi:

makako
10-24-2006, 08:51 AM
Hi, Im trying to use funtions in a spreadsheet inside a userform. The financial function (Present Value) asks for Values, Dates and the Rate. I use it to calculate cash flows etc. In Excel one has to select the advanced analysis to be able to use it but being the formula in the web component, how do i install it?

johnske
10-24-2006, 02:05 PM
... Yeah I noticed that I couldn't set a range to a range variable...which was weird. I don't understand the "holes" that the Spreadsheet component has with an actual excel worksheet.The OWC spreadsheet has a comprehensive Help file. Click on the S/S (in the VBE window) and press F1 to read it :)

malik641
10-24-2006, 02:31 PM
The OWC spreadsheet has a comprehensive Help file. Click on the S/S (in the VBE window) and press F1 to read it :)You're the man, johnske :thumb Thanks for the Tip!