Linking ComboBox to Filter in a protected sheet.

[MixedUpExcel]

Hi,

I have Sheet1 password protected.

I have approx 600 lines of product titles from Row 14 to 614.

I want to have the ComboBox so that the user can enter a word eg. blue and the filter will filter the 600 lines to just those that contain the word blue.

Bit already done:

Combobox linked to range so it shows the items when I click on the arrow at the side of the Combobox.

Combobox so that the user can click in and type text to filter down to what they are looking for - once they click on an item from the list.. I can't get it to link up to a filter.

I've found this piece of code with regards to filtering behind a password protected sheet.

With ActiveSheet
.Protect Password:="GERALD", AllowFiltering:=True
.EnableSelection = xlUnlockedCells
End With
Here: http://www.mrexcel.com/forum/excel-...s-password-protect-sheet-allow-filtering.html

Any solutions as to how I can get the filterbox to populate with the chosen item from the list in the Combobox?

Thanks.

Simon

 

[MixedUpExcel]

Hi,

I've been able to move on a bit with a slightly different approach:

I've linked the Combobox to Cell E12

I've then put the below code into the sheet module which will then pass the data from E12 into the filter and activate the filter (bear in mind that the filter has already been put in place - the macro is NOT adding a filter as well)

Private Sub MyFilter()


ActiveSheet.Unprotect Password:="mypass"


Worksheets("Sheet1").Range("E14:E700").AutoFilter _
 field:=1, _
 Criteria1:=Range("E12").Value, _
 VisibleDropDown:=False


ActiveSheet.Protect Password:="mypass", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub

Can anyone tell me how I can get the filter macro to automatically run when the user presses ENTER in the Combobox?

Thanks.

Simon