I have a workbook with all but one worksheet locked. I want to write a macro
to unlock specific worksheets only, determined by a dropdown box in the only
unlocked sheet. I'm not how to go about this though. Anyone have any ideas?

Thanks,
Eileen.

Not from a dropdown list but one way:-

Sub unprotect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
msg = "Do you want to unprotect " & ActiveSheet.Name
response = MsgBox(msg, vbYesNo)
If response = vbYes Then
ActiveSheet.unprotect
End If
Next ws
End Sub

Mike

"Eileen" wrote:

I have a workbook with all but one worksheet locked. I want to write a macro
to unlock specific worksheets only, determined by a dropdown box in the only
unlocked sheet. I'm not how to go about this though. Anyone have any ideas?

Thanks,
Eileen.

Thanks, that helped me come up with a workaround, until I realised that I
needed a loop.

I had the following code (where cell C10 contains the name of the first
sheet I want to unlock):

Worksheet1 = Range("C10").Value

Worksheets(Worksheet1).Activate
ActiveSheet.Unprotect

....which works on it's own, But when I try to put something similar in a
loop:


NoOfSheets = Range("C27").Value

For a = 10 To NoOfSheets

UnlockWorksheet = Range(Cells(3, a)).Value

Worksheets(UnlockWorksheet).Activate
ActiveSheet.Unprotect

Next a

....it trips up on the third line. I don't think it likes the Cells(3,a)
bit, but I'm not sure what to replace it with. Any ideas?

Thanks,
Eileen.



"Mike" wrote:

Not from a dropdown list but one way:-

Sub unprotect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
msg = "Do you want to unprotect " & ActiveSheet.Name
response = MsgBox(msg, vbYesNo)
If response = vbYes Then
ActiveSheet.unprotect
End If
Next ws
End Sub

Mike

"Eileen" wrote:

I have a workbook with all but one worksheet locked. I want to write a macro
to unlock specific worksheets only, determined by a dropdown box in the only
unlocked sheet. I'm not how to go about this though. Anyone have any ideas?

Thanks,
Eileen.

Hey, me again! Sorry!

I managed to work out the problem above. However it now doesn't like me
reusing the term UnlockWorksheet for each loop, but I can't figure out how to
work the loop funtion "a" into the name of the sheet to be unlocked.

Any help would be greatly appreciated!



"Eileen" wrote:

Thanks, that helped me come up with a workaround, until I realised that I
needed a loop.

I had the following code (where cell C10 contains the name of the first
sheet I want to unlock):

Worksheet1 = Range("C10").Value

Worksheets(Worksheet1).Activate
ActiveSheet.Unprotect

...which works on it's own, But when I try to put something similar in a
loop:


NoOfSheets = Range("C27").Value

For a = 10 To NoOfSheets

UnlockWorksheet = Range(Cells(3, a)).Value

Worksheets(UnlockWorksheet).Activate
ActiveSheet.Unprotect

Next a

...it trips up on the third line. I don't think it likes the Cells(3,a)
bit, but I'm not sure what to replace it with. Any ideas?

Thanks,
Eileen.



"Mike" wrote:

Not from a dropdown list but one way:-

Sub unprotect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
msg = "Do you want to unprotect " & ActiveSheet.Name
response = MsgBox(msg, vbYesNo)
If response = vbYes Then
ActiveSheet.unprotect
End If
Next ws
End Sub

Mike

"Eileen" wrote:

I have a workbook with all but one worksheet locked. I want to write a macro
to unlock specific worksheets only, determined by a dropdown box in the only
unlocked sheet. I'm not how to go about this though. Anyone have any ideas?

Thanks,
Eileen.