Protection Macro

I am completly ignorant and haven'y a clue about macro's and VBA...

Two things

I hope someone can help me with creating a password for all sheets at once
and then a second to 'unlock' them at once while keeping protection wherby
another cannot see the macro or itself has a password...

I know passwords can be cracked easily, someone has done it to the data I
was trying to protect! If I had an enormous random character password for the
protection...would this help? Are most crackers available limited to so many
character length etc?

please help....I now wish I had created the file in access!

many thanks in advance

Spheon

Spheon, try this,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub



To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium


It does not matter how long the password is, it can be broken very easy

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Spheon" <[email protected]> wrote in message
news:[email protected]...
>I am completly ignorant and haven'y a clue about macro's and VBA...
>
> Two things
>
> I hope someone can help me with creating a password for all sheets at once
> and then a second to 'unlock' them at once while keeping protection wherby
> another cannot see the macro or itself has a password...
>
> I know passwords can be cracked easily, someone has done it to the data I
> was trying to protect! If I had an enormous random character password for
> the
> protection...would this help? Are most crackers available limited to so
> many
> character length etc?
>
> please help....I now wish I had created the file in access!
>
> many thanks in advance
>
> Spheon

thank you....it is so frustrating that we can add passwords yet they are so
easily broken...

how would i hide the macro/protect it for instance I could press ctrl+j and
it would run the unprotect routine etc...

thanks again

"Paul B" wrote:

> Spheon, try this,
>
> Sub Protect_All_Sheets()
>
> Dim ws As Worksheet
>
> For Each ws In ThisWorkbook.Worksheets
>
> ws.Protect password:="123"
>
> Next ws
>
> End Sub
>
>
>
> Sub Unprotect_All_Sheets()
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
> ws.Unprotect password:="123"
> Next ws
> End Sub
>
>
>
> To put in this macro, from your workbook right-click the workbook's icon and
> pick View Code. This icon is to the left of the "File" menu this will open
> the VBA editor, in Project Explorer click on your workbook name, if you
> don't see it press CTRL + r to open the Project Explorer, then go to insert,
> module, and paste the code in the window that opens on the right hand side,
> press Alt and Q to close this window and go back to your workbook and press
> alt and F8, this will bring up a box to pick the Macro from, click on the
> Macro name to run it. If you are using excel 2000 or newer you may have to
> change the macro security settings to get the macro to run. To change the
> security settings go to tools, macro, security, security level and set it to
> medium
>
>
> It does not matter how long the password is, it can be broken very easy
>
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
>
> "Spheon" <[email protected]> wrote in message
> news:[email protected]...
> >I am completly ignorant and haven'y a clue about macro's and VBA...
> >
> > Two things
> >
> > I hope someone can help me with creating a password for all sheets at once
> > and then a second to 'unlock' them at once while keeping protection wherby
> > another cannot see the macro or itself has a password...
> >
> > I know passwords can be cracked easily, someone has done it to the data I
> > was trying to protect! If I had an enormous random character password for
> > the
> > protection...would this help? Are most crackers available limited to so
> > many
> > character length etc?
> >
> > please help....I now wish I had created the file in access!
> >
> > many thanks in advance
> >
> > Spheon
>
>
>

Spheon, if you put private in front of the macro name you want see it in the
list, but you can not use a short cut key then, you could ask for a password
to run this macro, the password for this and the sheets can be different if
you want, something like this

put a button on the sheet and assign the macro to it

Sub Protect_All_Sheets()
Dim MyStr1 As String, MyStr2 As String
Dim ws As Worksheet

With ActiveSheet
MyStr2 = ("123") 'This is the password to run the macro
MyStr1 = InputBox("Password Is Required To Run this Macro ")
If MyStr1 = MyStr2 Then

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="123" 'This is the password for each sheet
Next ws

Else
MsgBox ("Access Denied")
End If
End With
End Sub


You will also need to protect your VBA project so you can not see the
password(s) from there

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
select VBA project properties, protection, check lock project for viewing
and set a password. Press Alt and Q to close this window and go back to your
workbook and save and close the file. Be aware that this password can be
broken by third party software.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Spheon" <[email protected]> wrote in message
news:[email protected]...
> thank you....it is so frustrating that we can add passwords yet they are
> so
> easily broken...
>
> how would i hide the macro/protect it for instance I could press ctrl+j
> and
> it would run the unprotect routine etc...
>
> thanks again
>
> "Paul B" wrote:
>
>> Spheon, try this,
>>
>> Sub Protect_All_Sheets()
>>
>> Dim ws As Worksheet
>>
>> For Each ws In ThisWorkbook.Worksheets
>>
>> ws.Protect password:="123"
>>
>> Next ws
>>
>> End Sub
>>
>>
>>
>> Sub Unprotect_All_Sheets()
>> Dim ws As Worksheet
>> For Each ws In ThisWorkbook.Worksheets
>> ws.Unprotect password:="123"
>> Next ws
>> End Sub
>>
>>
>>
>> To put in this macro, from your workbook right-click the workbook's icon
>> and
>> pick View Code. This icon is to the left of the "File" menu this will
>> open
>> the VBA editor, in Project Explorer click on your workbook name, if you
>> don't see it press CTRL + r to open the Project Explorer, then go to
>> insert,
>> module, and paste the code in the window that opens on the right hand
>> side,
>> press Alt and Q to close this window and go back to your workbook and
>> press
>> alt and F8, this will bring up a box to pick the Macro from, click on the
>> Macro name to run it. If you are using excel 2000 or newer you may have
>> to
>> change the macro security settings to get the macro to run. To change the
>> security settings go to tools, macro, security, security level and set it
>> to
>> medium
>>
>>
>> It does not matter how long the password is, it can be broken very easy
>>
>> --
>> Paul B
>> Always backup your data before trying something new
>> Please post any response to the newsgroups so others can benefit from it
>> Feedback on answers is always appreciated!
>> Using Excel 2002 & 2003
>>
>>
>> "Spheon" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am completly ignorant and haven'y a clue about macro's and VBA...
>> >
>> > Two things
>> >
>> > I hope someone can help me with creating a password for all sheets at
>> > once
>> > and then a second to 'unlock' them at once while keeping protection
>> > wherby
>> > another cannot see the macro or itself has a password...
>> >
>> > I know passwords can be cracked easily, someone has done it to the data
>> > I
>> > was trying to protect! If I had an enormous random character password
>> > for
>> > the
>> > protection...would this help? Are most crackers available limited to so
>> > many
>> > character length etc?
>> >
>> > please help....I now wish I had created the file in access!
>> >
>> > many thanks in advance
>> >
>> > Spheon
>>
>>
>>

excellent...many thanks!

"Paul B" wrote:

> Spheon, if you put private in front of the macro name you want see it in the
> list, but you can not use a short cut key then, you could ask for a password
> to run this macro, the password for this and the sheets can be different if
> you want, something like this
>
> put a button on the sheet and assign the macro to it
>
> Sub Protect_All_Sheets()
> Dim MyStr1 As String, MyStr2 As String
> Dim ws As Worksheet
>
> With ActiveSheet
> MyStr2 = ("123") 'This is the password to run the macro
> MyStr1 = InputBox("Password Is Required To Run this Macro ")
> If MyStr1 = MyStr2 Then
>
> For Each ws In ThisWorkbook.Worksheets
> ws.Protect Password:="123" 'This is the password for each sheet
> Next ws
>
> Else
> MsgBox ("Access Denied")
> End If
> End With
> End Sub
>
>
> You will also need to protect your VBA project so you can not see the
> password(s) from there
>
> To protect the VBA project, from your workbook right-click the workbook's
> icon and pick View Code. This icon is to the left of the "File" menu this
> will open the VBA editor, in Project Explorer right click on your workbook
> name, if you don't see it press CTRL + r to open the Project Explorer then
> select VBA project properties, protection, check lock project for viewing
> and set a password. Press Alt and Q to close this window and go back to your
> workbook and save and close the file. Be aware that this password can be
> broken by third party software.
>
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Spheon" <[email protected]> wrote in message
> news:[email protected]...
> > thank you....it is so frustrating that we can add passwords yet they are
> > so
> > easily broken...
> >
> > how would i hide the macro/protect it for instance I could press ctrl+j
> > and
> > it would run the unprotect routine etc...
> >
> > thanks again
> >
> > "Paul B" wrote:
> >
> >> Spheon, try this,
> >>
> >> Sub Protect_All_Sheets()
> >>
> >> Dim ws As Worksheet
> >>
> >> For Each ws In ThisWorkbook.Worksheets
> >>
> >> ws.Protect password:="123"
> >>
> >> Next ws
> >>
> >> End Sub
> >>
> >>
> >>
> >> Sub Unprotect_All_Sheets()
> >> Dim ws As Worksheet
> >> For Each ws In ThisWorkbook.Worksheets
> >> ws.Unprotect password:="123"
> >> Next ws
> >> End Sub
> >>
> >>
> >>
> >> To put in this macro, from your workbook right-click the workbook's icon
> >> and
> >> pick View Code. This icon is to the left of the "File" menu this will
> >> open
> >> the VBA editor, in Project Explorer click on your workbook name, if you
> >> don't see it press CTRL + r to open the Project Explorer, then go to
> >> insert,
> >> module, and paste the code in the window that opens on the right hand
> >> side,
> >> press Alt and Q to close this window and go back to your workbook and
> >> press
> >> alt and F8, this will bring up a box to pick the Macro from, click on the
> >> Macro name to run it. If you are using excel 2000 or newer you may have
> >> to
> >> change the macro security settings to get the macro to run. To change the
> >> security settings go to tools, macro, security, security level and set it
> >> to
> >> medium
> >>
> >>
> >> It does not matter how long the password is, it can be broken very easy
> >>
> >> --
> >> Paul B
> >> Always backup your data before trying something new
> >> Please post any response to the newsgroups so others can benefit from it
> >> Feedback on answers is always appreciated!
> >> Using Excel 2002 & 2003
> >>
> >>
> >> "Spheon" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am completly ignorant and haven'y a clue about macro's and VBA...
> >> >
> >> > Two things
> >> >
> >> > I hope someone can help me with creating a password for all sheets at
> >> > once
> >> > and then a second to 'unlock' them at once while keeping protection
> >> > wherby
> >> > another cannot see the macro or itself has a password...
> >> >
> >> > I know passwords can be cracked easily, someone has done it to the data
> >> > I
> >> > was trying to protect! If I had an enormous random character password
> >> > for
> >> > the
> >> > protection...would this help? Are most crackers available limited to so
> >> > many
> >> > character length etc?
> >> >
> >> > please help....I now wish I had created the file in access!
> >> >
> >> > many thanks in advance
> >> >
> >> > Spheon
> >>
> >>
> >>
>
>
>

Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Spheon" <[email protected]> wrote in message
news:[email protected]...
> excellent...many thanks!
>
> "Paul B" wrote:
>
> > Spheon, if you put private in front of the macro name you want see it in
the
> > list, but you can not use a short cut key then, you could ask for a
password
> > to run this macro, the password for this and the sheets can be different
if
> > you want, something like this
> >
> > put a button on the sheet and assign the macro to it
> >
> > Sub Protect_All_Sheets()
> > Dim MyStr1 As String, MyStr2 As String
> > Dim ws As Worksheet
> >
> > With ActiveSheet
> > MyStr2 = ("123") 'This is the password to run the macro
> > MyStr1 = InputBox("Password Is Required To Run this Macro ")
> > If MyStr1 = MyStr2 Then
> >
> > For Each ws In ThisWorkbook.Worksheets
> > ws.Protect Password:="123" 'This is the password for each sheet
> > Next ws
> >
> > Else
> > MsgBox ("Access Denied")
> > End If
> > End With
> > End Sub
> >
> >
> > You will also need to protect your VBA project so you can not see the
> > password(s) from there
> >
> > To protect the VBA project, from your workbook right-click the
workbook's
> > icon and pick View Code. This icon is to the left of the "File" menu
this
> > will open the VBA editor, in Project Explorer right click on your
workbook
> > name, if you don't see it press CTRL + r to open the Project Explorer
then
> > select VBA project properties, protection, check lock project for
viewing
> > and set a password. Press Alt and Q to close this window and go back to
your
> > workbook and save and close the file. Be aware that this password can be
> > broken by third party software.
> >
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> > "Spheon" <[email protected]> wrote in message
> > news:[email protected]...
> > > thank you....it is so frustrating that we can add passwords yet they
are
> > > so
> > > easily broken...
> > >
> > > how would i hide the macro/protect it for instance I could press
ctrl+j
> > > and
> > > it would run the unprotect routine etc...
> > >
> > > thanks again
> > >
> > > "Paul B" wrote:
> > >
> > >> Spheon, try this,
> > >>
> > >> Sub Protect_All_Sheets()
> > >>
> > >> Dim ws As Worksheet
> > >>
> > >> For Each ws In ThisWorkbook.Worksheets
> > >>
> > >> ws.Protect password:="123"
> > >>
> > >> Next ws
> > >>
> > >> End Sub
> > >>
> > >>
> > >>
> > >> Sub Unprotect_All_Sheets()
> > >> Dim ws As Worksheet
> > >> For Each ws In ThisWorkbook.Worksheets
> > >> ws.Unprotect password:="123"
> > >> Next ws
> > >> End Sub
> > >>
> > >>
> > >>
> > >> To put in this macro, from your workbook right-click the workbook's
icon
> > >> and
> > >> pick View Code. This icon is to the left of the "File" menu this will
> > >> open
> > >> the VBA editor, in Project Explorer click on your workbook name, if
you
> > >> don't see it press CTRL + r to open the Project Explorer, then go to
> > >> insert,
> > >> module, and paste the code in the window that opens on the right hand
> > >> side,
> > >> press Alt and Q to close this window and go back to your workbook
and
> > >> press
> > >> alt and F8, this will bring up a box to pick the Macro from, click on
the
> > >> Macro name to run it. If you are using excel 2000 or newer you may
have
> > >> to
> > >> change the macro security settings to get the macro to run. To change
the
> > >> security settings go to tools, macro, security, security level and
set it
> > >> to
> > >> medium
> > >>
> > >>
> > >> It does not matter how long the password is, it can be broken very
easy
> > >>
> > >> --
> > >> Paul B
> > >> Always backup your data before trying something new
> > >> Please post any response to the newsgroups so others can benefit from
it
> > >> Feedback on answers is always appreciated!
> > >> Using Excel 2002 & 2003
> > >>
> > >>
> > >> "Spheon" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I am completly ignorant and haven'y a clue about macro's and VBA...
> > >> >
> > >> > Two things
> > >> >
> > >> > I hope someone can help me with creating a password for all sheets
at
> > >> > once
> > >> > and then a second to 'unlock' them at once while keeping protection
> > >> > wherby
> > >> > another cannot see the macro or itself has a password...
> > >> >
> > >> > I know passwords can be cracked easily, someone has done it to the
data
> > >> > I
> > >> > was trying to protect! If I had an enormous random character
password
> > >> > for
> > >> > the
> > >> > protection...would this help? Are most crackers available limited
to so
> > >> > many
> > >> > character length etc?
> > >> >
> > >> > please help....I now wish I had created the file in access!
> > >> >
> > >> > many thanks in advance
> > >> >
> > >> > Spheon
> > >>
> > >>
> > >>
> >
> >
> >