Macro to Protect OK, Unprotect now messed up!

[Stilla]

I thought I was being clever in recording a macro to protect workbooks with a
password, and then another to unprotect, using ctrl+ a diff letter everytime.

OK, it works, but now to unprotect I'm not even being asked for password!!
This happens whether I use the macro or the "unprotect" feature from the
menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!

Obviously, it sort of defeats the purpose of protecting in the first place,
if anyone can unprotect.

Is this happening, because I'm doing it on the same PC where my macro is
stored?

Help..

Thanks

[JE McGimpsey]

Did you protect the sheets with a password? Like

Sheets("Sheet1").Protect Password:="drowssap"

If not, then the sheet is protected without a password, and you won't be
prompted when you unprotect.

Protecting without a password is still useful - after all, the only
thing protecting actually does is prevent the casual user from screwing
up the worksheet. Anyone with the ability to find these newsgroups can
easily discover a method to bypass worksheet protection:

http://www.mcgimpsey.com/excel/removepwords.html






In article <[email protected]>,
Stilla <[email protected]> wrote:

> I thought I was being clever in recording a macro to protect workbooks with a
> password, and then another to unprotect, using ctrl+ a diff letter everytime.
>
> OK, it works, but now to unprotect I'm not even being asked for password!!
> This happens whether I use the macro or the "unprotect" feature from the
> menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!
>
> Obviously, it sort of defeats the purpose of protecting in the first place,
> if anyone can unprotect.
>
> Is this happening, because I'm doing it on the same PC where my macro is
> stored?
>
> Help..
>
> Thanks

[Rowan Drummond]

That is because when you record a macro to protect the workbook it does
not automatically record the password. If you go into the vbe your code
will look something like:

Sub Protct()
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

change it to:

Sub Protct()
ActiveWorkbook.Protect Structure:=True _
, Windows:=False, password:="mypassword"
End Sub

Similarly the macro to unprotect the book should look something like:

Sub Unprtct()
Dim pwd As String
pwd = InputBox("Enter Password...", "Unprotect Book")
If pwd = "mypassword" Then
ActiveWorkbook.Unprotect Password:=pwd
Else
MsgBox "Incorrect Password"
End If
End Sub

And now that you have the password in the VBA code you will probably
want to protect your VBE project so that this can't be viewed. In the
VBE use the menus to goto Tools>VBAProject Properties>Protection. Check
lock projet for viewing and supply a password.

Hope this helps
Rowan


Stilla wrote:
> I thought I was being clever in recording a macro to protect workbooks with a
> password, and then another to unprotect, using ctrl+ a diff letter everytime.
>
> OK, it works, but now to unprotect I'm not even being asked for password!!
> This happens whether I use the macro or the "unprotect" feature from the
> menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!
>
> Obviously, it sort of defeats the purpose of protecting in the first place,
> if anyone can unprotect.
>
> Is this happening, because I'm doing it on the same PC where my macro is
> stored?
>
> Help..
>
> Thanks

[Stilla]

AH HA! I did what you told me and it works! Yeah, I know protection is not
robust.. but it's a whole lot better than nothing!

I am totally new to macros, so let me ask another question...

When I "record" a macro - I am storing it in my Personal Macro worksheet -
(one of the choices). I am doing this instead of attaching the macro to the
file I'm working on, so that when I send this file on email, and someone else
opens it, a) they don't get those pesky security alerts, and 2) they won't be
able to open vba and read my password. IS THIS LOGIC CORRECT?

Thanks for sharing your knowledge!

"JE McGimpsey" wrote:

> Did you protect the sheets with a password? Like
>
> Sheets("Sheet1").Protect Password:="drowssap"
>
> If not, then the sheet is protected without a password, and you won't be
> prompted when you unprotect.
>
> Protecting without a password is still useful - after all, the only
> thing protecting actually does is prevent the casual user from screwing
> up the worksheet. Anyone with the ability to find these newsgroups can
> easily discover a method to bypass worksheet protection:
>
> http://www.mcgimpsey.com/excel/removepwords.html
>
>
>
>
>
>
> In article <[email protected]>,
> Stilla <[email protected]> wrote:
>
> > I thought I was being clever in recording a macro to protect workbooks with a
> > password, and then another to unprotect, using ctrl+ a diff letter everytime.
> >
> > OK, it works, but now to unprotect I'm not even being asked for password!!
> > This happens whether I use the macro or the "unprotect" feature from the
> > menu. As soon as I choose "unprotect" - VOILA! the sheet is unprotected!
> >
> > Obviously, it sort of defeats the purpose of protecting in the first place,
> > if anyone can unprotect.
> >
> > Is this happening, because I'm doing it on the same PC where my macro is
> > stored?
> >
> > Help..
> >
> > Thanks
>

[wmjenner]

Yes your logic is correct. Unless you store the macro in the actual workbook, it doesn't get sent when you e-mail the file to someone so they cannot open it. Conversely, I have saved macros that hide and unhide certain worksheets within a workbook (confidential stuff, for example) in my personal workbook. If you want to give access to someone to unhide and rehide, you have to then physically copy the macro to their own personal workbook (or, if they're not down the hall, send them the code).

[Stilla]

Hi ...THANKS... I beginning to understand the macro logic now. I just opened
the file I was working on yesterday, and my "personal macro file" also opened
in the background.

Is this Excel just being nice, or did I do something wrong?

"wmjenner" wrote:

>
> Yes your logic is correct. Unless you store the macro in the actual
> workbook, it doesn't get sent when you e-mail the file to someone so
> they cannot open it. Conversely, I have saved macros that hide and
> unhide certain worksheets within a workbook (confidential stuff, for
> example) in my personal workbook. If you want to give access to
> someone to unhide and rehide, you have to then physically copy the
> macro to their own personal workbook (or, if they're not down the hall,
> send them the code).
>
>
> --
> wmjenner
>
>
> ------------------------------------------------------------------------
> wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
> View this thread: http://www.excelforum.com/showthread...hreadid=491639
>
>

[Gord Dibben]

Stilla

Personal.xls will open when Excel opens.

If you have it set to "hidden" it will open in the background(won't show up on
Window) and your macros will be available for all open workbooks.


Gord Dibben Excel MVP

On Thu, 8 Dec 2005 06:10:06 -0800, Stilla <[email protected]>
wrote:

>Hi ...THANKS... I beginning to understand the macro logic now. I just opened
>the file I was working on yesterday, and my "personal macro file" also opened
>in the background.
>
>Is this Excel just being nice, or did I do something wrong?
>
>"wmjenner" wrote:
>
>>
>> Yes your logic is correct. Unless you store the macro in the actual
>> workbook, it doesn't get sent when you e-mail the file to someone so
>> they cannot open it. Conversely, I have saved macros that hide and
>> unhide certain worksheets within a workbook (confidential stuff, for
>> example) in my personal workbook. If you want to give access to
>> someone to unhide and rehide, you have to then physically copy the
>> macro to their own personal workbook (or, if they're not down the hall,
>> send them the code).
>>
>>
>> --
>> wmjenner
>>
>>
>> ------------------------------------------------------------------------
>> wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
>> View this thread: http://www.excelforum.com/showthread...hreadid=491639
>>
>>

[Stilla]

THANKS Gord! EXCEL IS SOOOOOOCOOOOL

"Gord Dibben" wrote:

> Stilla
>
> Personal.xls will open when Excel opens.
>
> If you have it set to "hidden" it will open in the background(won't show up on
> Window) and your macros will be available for all open workbooks.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 8 Dec 2005 06:10:06 -0800, Stilla <[email protected]>
> wrote:
>
> >Hi ...THANKS... I beginning to understand the macro logic now. I just opened
> >the file I was working on yesterday, and my "personal macro file" also opened
> >in the background.
> >
> >Is this Excel just being nice, or did I do something wrong?
> >
> >"wmjenner" wrote:
> >
> >>
> >> Yes your logic is correct. Unless you store the macro in the actual
> >> workbook, it doesn't get sent when you e-mail the file to someone so
> >> they cannot open it. Conversely, I have saved macros that hide and
> >> unhide certain worksheets within a workbook (confidential stuff, for
> >> example) in my personal workbook. If you want to give access to
> >> someone to unhide and rehide, you have to then physically copy the
> >> macro to their own personal workbook (or, if they're not down the hall,
> >> send them the code).
> >>
> >>
> >> --
> >> wmjenner
> >>
> >>
> >> ------------------------------------------------------------------------
> >> wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
> >> View this thread: http://www.excelforum.com/showthread...hreadid=491639
> >>
> >>
>