ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   unprotect next sheet (https://www.excelbanter.com/excel-programming/392699-unprotect-next-sheet.html)

ADK
unprotect next sheet
 
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").Current Region.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").Current Region.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub



Mike H
unprotect next sheet
 
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

"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub




Tom Ogilvy
unprotect next sheet
 
My guess would be that the password is not "password" or whatever string you
are really using.

--
Regards,
Tom Ogilvy


"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub




ADK
unprotect next sheet
 
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?


"Tom Ogilvy" wrote in message
...
My guess would be that the password is not "password" or whatever string
you
are really using.

--
Regards,
Tom Ogilvy


"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub

Tom Ogilvy
unprotect next sheet
 
Try being more explicit
Activesheet.unprotect Password:="ABC"

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

--
Regards,
Tom Ogilvy



"ADK" wrote:

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?


"Tom Ogilvy" wrote in message
...
My guess would be that the password is not "password" or whatever string
you
are really using.

--
Regards,
Tom Ogilvy


"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub

ADK
unprotect next sheet
 
still failed. stops at this line of code with that error message:

With Cells.SpecialCells(xlCellTypeVisible)


"Tom Ogilvy" wrote in message
...
Try being more explicit
Activesheet.unprotect Password:="ABC"

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

--
Regards,
Tom Ogilvy



"ADK" wrote:

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?


"Tom Ogilvy" wrote in message
...
My guess would be that the password is not "password" or whatever
string
you
are really using.

--
Regards,
Tom Ogilvy


"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub

ADK
unprotect next sheet
 
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


"ADK" wrote in message
...
still failed. stops at this line of code with that error message:

With Cells.SpecialCells(xlCellTypeVisible)


"Tom Ogilvy" wrote in message
...
Try being more explicit
Activesheet.unprotect Password:="ABC"

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

--
Regards,
Tom Ogilvy



"ADK" wrote:

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?


"Tom Ogilvy" wrote in message
...
My guess would be that the password is not "password" or whatever
string
you
are really using.

--
Regards,
Tom Ogilvy


"ADK" wrote:

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").Current Region.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").Current Region.Rows.Count +
1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub


All times are GMT +1. The time now is 07:04 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com