List, Filter Sort?

[tomrector]

Excel 2003

I have sheet (in second TAB, named "Tickets". 300 lines total
there are 3 lines of labels
there are 295 lines of data (cols A - AP)
There are two line of summary
300 total lines
Also sheet is locked/protected for col C and cols AK-AP

I want the user to be able to "click and pick" one of the values in
col A, rows 4 thru 297
taking her to that line so she can update values in cols F-AJ. I
have tried Validation and
filters, but can't get it to work.

Is there a VB routine that I can use to unlock spreadsheet, set up
list validation on row 4 thur 297 (col A as target), allow data entry
in col F-AJ, then relock the sheet ?

Thanks for any help/direction,,,,,,

Tom Rector

 

[Gord Dibben]

Tom

Couple of methods to unprotect/reprotect.

Sub Do_things()
ActiveSheet.Unprotect Password:="justme"
'do your things here........you could record a macro to get the code
ActiveSheet.Protect Password:="justme"
End Sub

Or have a look at this sample code from Dave Peterson using the
"userinterfaceonly" method.

Option Explicit
Sub auto_open()
dim mySheetNames as variant
dim iCtr as long
mySheetnames = array("sheet1","sheet2","sheet3","sheet4")
for ictr = lbound(mysheetnames) to ubound(mysheetnames)
With Worksheets(mysheetnames(ictr))
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
next ictr
End Sub


Gord Dibben MS Excel MVP