Problem using VBA for Excel to protect a Workbook with a password.
I have used this code for te past 5 years without any trouble:
Option Explicit
Public Sub ProtectAllSheets()
Dim intSheet As Integer
Dim blnVisible As Boolean
Dim strCurrentSheet As String
Dim blnSheetProtectionState As Boolean
Dim strSheetProtectionPassword As String
Dim blnScreenUpdate As Boolean
Dim intCalc As Integer
blnScreenUpdate = application.ScreenUpdating
intCalc = application.Calculation
application.ScreenUpdating = False
application.Calculation = xlCalculationManual
strSheetProtectionPassword = "q"
strCurrentSheet = ActiveSheet.Name
For intSheet = 1 To Sheets.count
blnVisible = Sheets(intSheet).Visible
Sheets(intSheet).Visible = True
Sheets(intSheet).Protect Password:=strSheetProtectionPassword
Sheets(intSheet).Visible = blnVisible
Next intSheet
Sheets(strCurrentSheet).Select
application.ScreenUpdating = blnScreenUpdate
application.Calculation = intCalc
End Sub
Public Sub UnProtectAllSheets()
Dim intSheet As Integer, blnVisible As Boolean, strCurrentSheet As String
Dim blnSheetProtectionState As Boolean, strSheetProtectionPassword As String
Dim blnScreenUpdate As Boolean, intCalc As Integer
blnScreenUpdate = application.ScreenUpdating
intCalc = application.Calculation
application.ScreenUpdating = False
application.Calculation = xlCalculationManual
strSheetProtectionPassword = "q"
strCurrentSheet = ActiveSheet.Name
For intSheet = 1 To Sheets.count
blnVisible = Sheets(intSheet).Visible
Sheets(intSheet).Visible = True
Sheets(intSheet).Unprotect Password:=strSheetProtectionPassword
Sheets(intSheet).Visible = blnVisible
Next intSheet
Sheets(strCurrentSheet).Select
application.ScreenUpdating = blnScreenUpdate
application.Calculation = intCalc
End Sub
You will need to add in a line to protect the workbook, but that should be
easy.
ALTERNATIVELY, here is another method:
Option Explicit
Public Sub UnProtectWS()
Dim i As Integer
ActiveWorkbook.Unprotect password:="business"
For i = 1 To Sheets.Count
Sheets(i).Unprotect password:="business"
Next i
End Sub
Public Sub ProtectWS()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Protect password:="business"
Next i
ActiveWorkbook.Protect password:="business"
End Sub
Hope this helps you.
--
Alan Hutchins
"Alan Smith" wrote:
I've developed a workbook with a number of sheets. I want to prevent users
from copying or deleting sheets without suitable control so I used Excel VBA
code to protect and unprotect the workbook. The problem is that when I
protect the workbook with a password, when I get back to Excel I am able to
unprotect the workbook without providing the password. Can anyone explain
what I'm doing wrong please? The code is:
dim wkbkone as workbook
Set wkbkone = Application.ActiveWorkbook
wkbkone.Protect "abcd"
wkbkone.Protect "abcd"
ActiveWorkbook.Protect Structu=True, Windows:=True
|