AutoFilter Protected Sheet

[jzeusj]

This is a 2 part question:
1) I have a sheet I lock down so users cannot change row 1. There is some other stuff going on behind the scenes, but once they've entered their password, it filters the sheet and locks down certain columns and row 1. I would like to still allow the user to use Autofilter after the sheet is locked. The example table with code is below. Once the sheet is protected, the user cannot use the Autofilter to change column 2 (last names). Is there any work around or suggestions for this?

2) Right now, my code has the columns hard-coded in for which ones to lock. The example table/code is just a snapshot. The actual table is much larger and the columns change periodically. I added the "x" at the top of each column I want to unlock (i.e. users should only be allowed to modify columns with an "x" at the top). How would I loop through that? Part of the problem is there are gaps in between the x's, my idea was to loop through the x's and lock each column that has an x at the top. I've been playing with End(xlToRight), but can't quite get it.

Sub LockCells()
With Sheets(1)
    .Unprotect Password:="test"
    .AutoFilterMode = False
    .Cells.Locked = True
    .Columns("B:B").Locked = False
    .Columns("D:D").Locked = False
    .Range("B1").EntireRow.Locked = True
    .Cells.AutoFilter Field:=1, Criteria1:="a"
    .Protect Password:="test"
End With
End Sub

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th><th>Lock</th></tr> <tr><td> </td><td> </td><td> </td><td>x</td><td> </td><td>x</td><td> </td><td>x</td><td> </td></tr> <tr><td>a</td><td>smith</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>a</td><td>jones</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>a</td><td>anderson</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>b</td><td>smith</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>b</td><td>jones</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>b</td><td>anderson</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>c</td><td>smith</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>c</td><td>jones</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>c</td><td>anderson</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>d</td><td>smith</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr> <tr><td>d</td><td>jones</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td><td>Unlock</td><td>Lock</td></tr></table>

 

[jzeusj]

I solved it myself. If anybody is trying to do something similar and needs help, let me know. I have a solution.