Autofilter in Userform2.Spreadsheet1

[m0atz]

Afternoon all,

I'm having some trouble with the code to autofilter the results in spreadsheet1 that i've added in to a userform (userform2).

I have columns A - F showing with all rows, which works fine.

I have been able to enable the autofilter, as follows:

With Spreadsheet1
rngTarget.Value = rngSource.Value
.Columns("A:Z").EntireColumn.AutoFit
.Columns("D:D").NumberFormat = "0"
.Columns("A:F").AutoFilter
End With

The autofilter property however appears different to that of a 'normal worksheet'. What i want is for the autofilter to filter the result for the value of userform1.combobox1.value

is this possible?

I've tried the following:

.columns("A:F").AutoFilter, field:=1, criteria1:= userform1.combobox1.value

but i get a weird message when i run the userform, saying that Field 1 and Criteria 1 must be omitted or equal to " " which doesnt work for me...

Any clues??

Much appreciated.

colin

 

[RoryA]

The OWC spreadsheet autofilter works completely differently. You need something like:

With Spreadsheet1.Sheets(1)
rngTarget.Value = rngSource.Value 
.Columns("A:Z").EntireColumn.AutoFit 
.Columns("D:D").NumberFormat = "0" 
.Columns("A:F").AutoFilter
   With .AutoFilter.Filters(1).Criteria
      .FilterFunction = ssFilterFunctionInclude
      .Add userform1.combobox1.value
   End With
End With

 

[m0atz]

That very very nearly works!!

It selects the value in the drop down list for the filter, how can we automate the pressing of the ok button, i.e. to select this value in the filter?

Cheers

Colin

 

[RoryA]

Oops - managed to miss the important bit!

With Spreadsheet1.Sheets(1)
rngTarget.Value = rngSource.Value 
.Columns("A:Z").EntireColumn.AutoFit 
.Columns("D:D").NumberFormat = "0" 
.Columns("A:F").AutoFilter
   With .AutoFilter
      With .Filters(1).Criteria
         .FilterFunction = ssFilterFunctionInclude
         .Add userform1.combobox1.value
      End With
      .Apply
   End With
End With

 

[m0atz]

This works perfect - very, very much appreciated!!!

 

[RoryA]

Glad to help. I will never understand why the default action for a filter in the OWC spreadsheet control is to exclude the value you specify!

 

[m0atz]

Is there anyway to remove the option to select "sheet 1, sheet 2" etc when its in live mode (i've seen some code floating around on this, but it doesnt seem to work - perhaps i'm putting it in the wrong place?

 

[RoryA]

You can turn that off at design time in the Custom Properties section, on the Workbook tab.

 

[m0atz]

ok, i must be going mad now? !

I've right clicked on the spreadsheet1 in design mode, properties, but there's no option to control anything like that..?

i've also tried

with spreadsheet1
.displayworkbooktabs = false
end with

but that doesnt have any effect..

Sorry to be a pain!!

Cheers

 

[m0atz]

Oh my word, sorry, i've just realised how simple that is!!!

Doh!!

Many thanks for your patience!