protect / unprotect worksheets using VBA
 

My workbook includes several worksheets. I'm using the following code
to unprotect and then protect the contents of these worksheets as I
write to them.

DatabaseWorksheet.Unprotect Password:=DwgNumLogPwd
InputFormWorksheet.Select
DatabaseWorksheet.Cells(DestinationRow, 1).Value = AssignDate
DatabaseWorksheet.Protect Password:=DwgNumLogPwd

This code is executed when InputFormWorksheet is the active worksheet.
Thus there is a toggling back and forth between worksheets.

Does anyone know of a cleaner way to protect worksheets? Bear in mind
the worksheets being protected need to remain visible.

Thanks.

Kevin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

protect / unprotect worksheets using VBA
 

There isn't any toggling in the code you show if InputFormWorksheet is
already the active Sheet.

--
Regards,
Tom Ogilvy

ExcelUserinPA wrote in message
...
My workbook includes several worksheets. I'm using the following code
to unprotect and then protect the contents of these worksheets as I
write to them.

DatabaseWorksheet.Unprotect Password:=DwgNumLogPwd
InputFormWorksheet.Select
DatabaseWorksheet.Cells(DestinationRow, 1).Value = AssignDate
DatabaseWorksheet.Protect Password:=DwgNumLogPwd

This code is executed when InputFormWorksheet is the active worksheet.
Thus there is a toggling back and forth between worksheets.

Does anyone know of a cleaner way to protect worksheets? Bear in mind
the worksheets being protected need to remain visible.

Thanks.

Kevin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

protect / unprotect worksheets using VBA
 

I also have a workbook with all sheets protected (except 2 hidden sheets).
Here is the code I use to turn protection on and off. However my approach is
different in I unprotect all the sheets at once while I am working on it.
When I'm done I protect them again (except 2 hidden) before I save It. I
assigned the macros shortcut keys.

Sub unpro()
' Keyboard Shortcut: Ctrl+u
'
Dim w As Worksheet
For Each w In Worksheets
w.Unprotect ("password")
Next
End Sub

Sub protect()
'
' Keyboard Shortcut: Ctrl+p
'
Dim w As Worksheet
For Each w In Worksheets
w.protect Password:="password"
Next
Sheet20.Unprotect ("password")
Sheet65.Unprotect ("password")
End Sub

Most of the people who use my workbook don't have a clue what a macro is or
what to do with it if they found it in a list. But, to keep curious others
from using the unprotect macro in the Tools/Macro list, simply put a Rem
mark (') before the subs name before you save, and it will not be in the
list. To further protect from those who would go into the VBEditor, password
your code so only you can remove the Rem mark.

Mike
"ExcelUserinPA" wrote in message
...
My workbook includes several worksheets. I'm using the following code
to unprotect and then protect the contents of these worksheets as I
write to them.

DatabaseWorksheet.Unprotect Password:=DwgNumLogPwd
InputFormWorksheet.Select
DatabaseWorksheet.Cells(DestinationRow, 1).Value = AssignDate
DatabaseWorksheet.Protect Password:=DwgNumLogPwd

This code is executed when InputFormWorksheet is the active worksheet.
Thus there is a toggling back and forth between worksheets.

Does anyone know of a cleaner way to protect worksheets? Bear in mind
the worksheets being protected need to remain visible.

Thanks.

Kevin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/