Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
I want to protect every sheet in a workbook with a password. I understand
this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Hi,
Press Alt + F11 to show the VB Editor window. Find "Book1" or whatever your workbbok is called, and right-click it to "Insert Module" - paste this into the module: Sub demo() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect "blahblah" Next ws End Sub changing "blahblah" to whatever password you want. That's it. Sam "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Wow, that seems to be just the ticket! I will try fiddling with it to
customise. Many thanks! "Sam Wilson" wrote: Hi, Press Alt + F11 to show the VB Editor window. Find "Book1" or whatever your workbbok is called, and right-click it to "Insert Module" - paste this into the module: Sub demo() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect "blahblah" Next ws End Sub changing "blahblah" to whatever password you want. That's it. Sam "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Step by step
1). From Excel --Tools--Macro--Visual Basic Editor 2). From VBE --View--Project Explorer (if not already displayed) 3). Double Click "ThisWorkbook" Module 4). Paste the below Option Explicit ' Change "Password" to a password of your choice Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Protect "Password", True, True End Sub Private Sub Workbook_Open() Dim QueryUser As String Const Password As String = "Password" QueryUser = InputBox("Enter Password", Application.Name) If QueryUser = Password Then ThisWorkbook.Unprotect Password Else MsgBox "Sorry, No Workbook for you!", vbCritical, Application.Name ThisWorkbook.Close SaveChanges:=False End If End Sub "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Thanks Jeff, but I tried Sam's macro before seeing your message. Sam's seems
to do the trick, but what is the difference between yours? Yours seems much longer. "Jeff" wrote: Step by step 1). From Excel --Tools--Macro--Visual Basic Editor 2). From VBE --View--Project Explorer (if not already displayed) 3). Double Click "ThisWorkbook" Module 4). Paste the below Option Explicit ' Change "Password" to a password of your choice Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Protect "Password", True, True End Sub Private Sub Workbook_Open() Dim QueryUser As String Const Password As String = "Password" QueryUser = InputBox("Enter Password", Application.Name) If QueryUser = Password Then ThisWorkbook.Unprotect Password Else MsgBox "Sorry, No Workbook for you!", vbCritical, Application.Name ThisWorkbook.Close SaveChanges:=False End If End Sub "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
You might want to add a second macro to unprotect all sheets:
Sub demo2() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Unprotect "blahblah" Next ws End Sub Sam "E" wrote: Wow, that seems to be just the ticket! I will try fiddling with it to customise. Many thanks! "Sam Wilson" wrote: Hi, Press Alt + F11 to show the VB Editor window. Find "Book1" or whatever your workbbok is called, and right-click it to "Insert Module" - paste this into the module: Sub demo() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect "blahblah" Next ws End Sub changing "blahblah" to whatever password you want. That's it. Sam "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Good plan, thanks. I assume 'demo2' is the name of the macro, which I can
change, and then add in a shortcut in the Options menu? (I'm Office 2007.) "Sam Wilson" wrote: You might want to add a second macro to unprotect all sheets: Sub demo2() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Unprotect "blahblah" Next ws End Sub Sam "E" wrote: Wow, that seems to be just the ticket! I will try fiddling with it to customise. Many thanks! "Sam Wilson" wrote: Hi, Press Alt + F11 to show the VB Editor window. Find "Book1" or whatever your workbbok is called, and right-click it to "Insert Module" - paste this into the module: Sub demo() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect "blahblah" Next ws End Sub changing "blahblah" to whatever password you want. That's it. Sam "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Mine just protects the entire workbook, When opened it promts the user for a
password. If its right it unprotect the entire workbook, if not it notifies the user then closes. Before the worbook closes it protects the workbook again. Sams Loops through protecting\Unprotecting each sheet one at a time. "E" wrote: Thanks Jeff, but I tried Sam's macro before seeing your message. Sam's seems to do the trick, but what is the difference between yours? Yours seems much longer. "Jeff" wrote: Step by step 1). From Excel --Tools--Macro--Visual Basic Editor 2). From VBE --View--Project Explorer (if not already displayed) 3). Double Click "ThisWorkbook" Module 4). Paste the below Option Explicit ' Change "Password" to a password of your choice Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Protect "Password", True, True End Sub Private Sub Workbook_Open() Dim QueryUser As String Const Password As String = "Password" QueryUser = InputBox("Enter Password", Application.Name) If QueryUser = Password Then ThisWorkbook.Unprotect Password Else MsgBox "Sorry, No Workbook for you!", vbCritical, Application.Name ThisWorkbook.Close SaveChanges:=False End If End Sub "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
I see. I want users to be able to open the file and use the information, just
not edit it, so I think Sam's is more appropriate for me. Thank you. "Jeff" wrote: Mine just protects the entire workbook, When opened it promts the user for a password. If its right it unprotect the entire workbook, if not it notifies the user then closes. Before the worbook closes it protects the workbook again. Sams Loops through protecting\Unprotecting each sheet one at a time. "E" wrote: Thanks Jeff, but I tried Sam's macro before seeing your message. Sam's seems to do the trick, but what is the difference between yours? Yours seems much longer. "Jeff" wrote: Step by step 1). From Excel --Tools--Macro--Visual Basic Editor 2). From VBE --View--Project Explorer (if not already displayed) 3). Double Click "ThisWorkbook" Module 4). Paste the below Option Explicit ' Change "Password" to a password of your choice Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Protect "Password", True, True End Sub Private Sub Workbook_Open() Dim QueryUser As String Const Password As String = "Password" QueryUser = InputBox("Enter Password", Application.Name) If QueryUser = Password Then ThisWorkbook.Unprotect Password Else MsgBox "Sorry, No Workbook for you!", vbCritical, Application.Name ThisWorkbook.Close SaveChanges:=False End If End Sub "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Me too, No Worries.
"E" wrote: I see. I want users to be able to open the file and use the information, just not edit it, so I think Sam's is more appropriate for me. Thank you. "Jeff" wrote: Mine just protects the entire workbook, When opened it promts the user for a password. If its right it unprotect the entire workbook, if not it notifies the user then closes. Before the worbook closes it protects the workbook again. Sams Loops through protecting\Unprotecting each sheet one at a time. "E" wrote: Thanks Jeff, but I tried Sam's macro before seeing your message. Sam's seems to do the trick, but what is the difference between yours? Yours seems much longer. "Jeff" wrote: Step by step 1). From Excel --Tools--Macro--Visual Basic Editor 2). From VBE --View--Project Explorer (if not already displayed) 3). Double Click "ThisWorkbook" Module 4). Paste the below Option Explicit ' Change "Password" to a password of your choice Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Protect "Password", True, True End Sub Private Sub Workbook_Open() Dim QueryUser As String Const Password As String = "Password" QueryUser = InputBox("Enter Password", Application.Name) If QueryUser = Password Then ThisWorkbook.Unprotect Password Else MsgBox "Sorry, No Workbook for you!", vbCritical, Application.Name ThisWorkbook.Close SaveChanges:=False End If End Sub "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Novice - using macro to protect with password
Can you write the macro to ask for you to input the password so it isn't
embedded in the macro? "Sam Wilson" wrote: You might want to add a second macro to unprotect all sheets: Sub demo2() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Unprotect "blahblah" Next ws End Sub Sam "E" wrote: Wow, that seems to be just the ticket! I will try fiddling with it to customise. Many thanks! "Sam Wilson" wrote: Hi, Press Alt + F11 to show the VB Editor window. Find "Book1" or whatever your workbbok is called, and right-click it to "Insert Module" - paste this into the module: Sub demo() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect "blahblah" Next ws End Sub changing "blahblah" to whatever password you want. That's it. Sam "E" wrote: I want to protect every sheet in a workbook with a password. I understand this is tricky, so I will be satisfied if I can write a macro to password protect every sheet, which will save me typing in the password twice for every sheet. I have used macros before by simply recording them, but have no idea how to write the code myself. Please could someone give me some very simple instructions how to write an appropriate macro, or edit one I can easily record. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Protect MACRO with Password | Excel Programming | |||
Password Protect Macro | Excel Programming | |||
password protect macro | Excel Programming | |||
Password Protect Macro? | Excel Programming | |||
Password protect macro | Excel Programming |