unprotect next sheet

A

ADK

I have this code below which does not work with the next sheet. I think it
is ActiveSheet.Unprotect ("password") for the CompletionTable sheet. What is
wrong with the code not making it work past the first sheet?

Private Sub test_Click()
ActiveSheet.Unprotect ("password")
Dim lHiddenRws As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRws = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRws, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("password")
Dim lHiddenRwsb As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRwsb = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRwsb, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub
 
M

Mike H

Hi,

Does this help?

Sub stance()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select

'Do your stuff here

Next wSheet
End Sub

Mike
 
T

Tom Ogilvy

My guess would be that the password is not "password" or whatever string you
are really using.
 
A

ADK

the code goes to debug mode with an eror:

Run-time error '1004'

You cannot use this command on a protected sheet. To uprotect........


So the code is not unprotecting the second sheet before moving on with the
code


any ideas?
 
T

Tom Ogilvy

Try being more explicit
Activesheet.unprotect Password:="ABC"

this is not an asynchronous command, so it will be executed before moving on.
 
A

ADK

still failed. stops at this line of code with that error message:

With Cells.SpecialCells(xlCellTypeVisible)
 
A

ADK

I did a test with the following code and this one worked (both sheets
unprotected before test and after test both sheets were protected)....so
that tells me something other than the protect/uprotect portion is failing.

any ideas?


Private Sub TestMeNow_Click()
ActiveSheet.Unprotect ("123")
ActiveSheet.Protect ("123")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("123")
ActiveSheet.Protect ("123")
Sheets("PDSR").Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top