Please help!!! Using code to password-protect and unprotect...

[Hawk]

With help from this group, I am using the following code to hide empty
columns in my spreadsheet. It works when my sheet is not protected,
however, when I deploy this sheet for use by others it will need to be
password-protected. What can I add to the code to unprotect the sheet
prior to hiding the columns and then re-protect the sheet after the
columns have been hidden? Will my password-protection be preserved?
Please help...

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82


Application.ScreenUpdating = False
With Sheets("Box")
Dim col As Range
For Each col In .Range("C10:AF82").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0

Next
End With
Application.ScreenUpdating = True
End Sub

 

[KL]

Hi Hawk,

Try this:

Sub Hide_EmptyColumns()
'To hide columns with no data in rows 10:82
Application.ScreenUpdating = False
Dim col As Range, Pass As String
Pass = "password"
With Sheets("Box")
.Protect Pass
For Each col In .Range("C10:AF82").Columns
col.EntireColumn.Hidden = _
Application.Sum(col) = 0
Next
.Unprotect Pass
End With
Application.ScreenUpdating = True
End Sub


Regards,
KL

 

[STEVE BELL]

Here's something from Greg Wilson.

It does 2 things
1. The user can only select unprotected cells
2. This part
WS.Protect 'Password:="wxyz", UserInterfaceOnly:=True
allows code to run on a protected sheet

see if this helps...


Select UnProtected Cells ONLY

This will prevent users from clicking on protected cells on all

worksheets. Therefore, the warning message will not appear. The code

must be enterred in the ThisWorkbook module.



Note that the EnableSelection property must be reset each time the

workbook is opened as it defaults to xlNoRestrictions. The worksheet(s)

must first be unprotected to set the EnableSelection property and then

must be protected for it to take effect.



Private Sub Workbook_Open()

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

WS.Unprotect 'Password:="wxyz"

WS.EnableSelection = xlUnlockedCells

WS.Protect 'Password:="wxyz", UserInterfaceOnly:=True

Next

End Sub



Regards,

Greg Wilson 5/3/03

 

[Hawk]

It worked!! Thanks KL...

 

[STEVE BELL]

Glad you like it...

This code has been very useful for me...

Thanks go to Greg Wilson...

 

[Tom Ogilvy]

He said thanks to **KL** -- maybe it sounded like Steve Bell <g>

 

[STEVE BELL]

My miss-read...