Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using VBA for Excel to protect a Workbook with a password.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using VBA for Excel to protect a Workbook with a password.
Hi
Look at this line: ActiveWorkbook.Protect Password:="abcd", Structu=True, Windows:=True Regards, Per "Alan Smith" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using VBA for Excel to protect a Workbook with a passw
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. "Per Jessen" wrote: Hi Look at this line: ActiveWorkbook.Protect Password:="abcd", Structu=True, Windows:=True Regards, Per "Alan Smith" skrev i meddelelsen ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove password protect to open Excel 2007 workbook | Excel Discussion (Misc queries) | |||
How do I password protect an excel workbook? | Excel Discussion (Misc queries) | |||
How do I password protect a workbook in Excel? | Excel Discussion (Misc queries) | |||
How to password protect several excel workbook pages using a macro | Excel Programming | |||
password protect on workbook problem | Excel Programming |