protecting worksheets

[Anthony]

Hi All

On my sheet 'Data' I have a data validation drop down menu that contains 7
'options' for the user to pick. Each option is linked to a seperate marco and
is run when the option is chosen .

What I would like is some code that will protect the following sheet names
when the workbook is first opened..

Data
Sheet 5


Then each time an option is chosen from my validation list on sheet 'data' I
want both the above sheets to be unprotected, and finally when the macro is
run and complete then both sheets to be protected again. Oh and when the
workbook is closed then unprotect the sheets once more.

Any help with code and exactly where to place it so that it works would be
very grateful

thanks

 

[Otto Moehrbach]

Anthony
The code to protect/unprotect those 2 sheets is simple enough, but the
code to unprotect those sheets when a selection is made in your drop-down
list is another matter. The latter code has to go inside of the macro(s)
that fires when a selection is made. You probably have a single sheet event
macro that fires when a selection is made and that macro runs some code that
is dependent on the selection made. You need to find that macro and post
it. If it is a sheet macro, you can access it by right-clicking on the
sheet tab, and select View Code. That will take you to the sheet module for
your sheet. Copy whatever is in that module and post it.
The code to protect/unprotect those 2 sheets when the file is opened/closed
is shown below. These 2 macros have to go in the workbook module. In
versions before 2007, you can access that module by right-clicking on the
Excel icon that is immediately to the left of the word "File" in the menu
that runs across the top of the screen. Select View Code and paste these
macros into that module. Note that I put a space before the "5" of "Sheet
5". I did so because that's what you did in your post. The sheet names in
the macros have to match exactly the sheet names in your file. Let me add
that it doesn't make much sense to Unprotect those sheets when you close the
file and Protect them when you open the file since nothing can happen to the
file between closing it and opening it.
Private Sub Workbook_Open()
Sheets("Data").Protect
Sheets("Sheet 5").Protect
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Data").Unprotect
Sheets("Sheet 5").Unprotect
End Sub