Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List, Filter Sort?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List, Filter Sort?
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 On Thu, 24 Jan 2008 11:23:08 -0800 (PST), wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
Filter, sort and sum | Excel Discussion (Misc queries) | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . | Excel Worksheet Functions |