Prev: Unexpected internal error or not enough memory
Next: Excel 2007 query - Access MDB question
From: E on 25 Jan 2010 11:18
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.
From: Sam Wilson on 25 Jan 2010 11:29
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.
From: E on 25 Jan 2010 11:34
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.
From: Jeff on 25 Jan 2010 11:34
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.
From: E on 25 Jan 2010 11:41
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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Unexpected internal error or not enough memory
Next: Excel 2007 query - Access MDB question