Unlocking specific worksheets
 

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.

Unlocking specific worksheets
 

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.

Unlocking specific worksheets
 

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.

Unlocking specific worksheets
 

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.