Problem using VBA for Excel to protect a Workbook with a password.

[Alan Smith]

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 Structure:=True, Windows:=True

 

[Per Jessen]

Hi

Look at this line:

ActiveWorkbook.Protect Password:="abcd", Structure:=True, Windows:=True

Regards,
Per

 

[Alan Hutchins]

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 Smith]

Thanks for the help with this. It worked fine. I've onlty been using Excel
VBA for 2 weeks (self-taught) and I'm still learning!

Thanks to Alan Hutchins too.

 

[Cheers]

Hi,
Yes. It can be done. I have written a VBA program. All you need is to input
<Filename>, <Path> and <Password> for the file via VBA. You may edit anything
in the hidden mode via VBA, then save it wihout user have a chance to view
what is going on.

Email to:
(e-mail address removed)
for more details.

Cheers