Protecting Password Info within a macro / Workaround

Hi there,

I've got a sheet (call it holidays.xls) that uses the following macro to
select and password-protect non-blank cells in a worksheet from unauthorised
amendments.

ActiveSheet.Unprotect Password:="password"
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="password", DrawingObjects:=True,
Contents:=True, Scenarios:=True
MsgBox "Data Cells Locked Down"

The macro itself works fine; my problem lies in the fact that all users can
open the macro if they so desire and view the password, thereby allowing them
to carry out unauthorised edits.

I've tried:

a) storing the macro in the personal macro workbook (personal.xls) of those
authorised to make amendments (rather than in holidays.xls). However, when
Excel is closed and re-opened, personal.xls does not launch automatically,
causing the macro to be unavailable by default. Is there a workaround whereby
the macro can be made available on launch of holidays.xls, only to nominated
persons, whether stored in the workbook, the personal macro workbook or
otherwise? (I know I can auto-launch personal.xls on Excel startup but was
hoping for something a little less clunky)

and I've also tried

b) constructing the macro whereby a(n unspecified) password is requested to
protect (as well as unprotect) the sheet. This does not seem to be possible -
Naturally, the password dialog presents during the unprotection phase, but I
cannot get the password dialog to present during the re-protection phase. Can
this be done?

Any advice appreciated,
Thanks in advance.
Ray.

Hi Ray,

> The macro itself works fine; my problem lies in the fact that all
> users can open the macro if they so desire and view the password,

set a vba-project password, users will not be able to open and see your
macros.

Set the pwd in VBA-Editor/Extras/PropertiesofVBAProject/Protection -
hide the project and enter a password.

This password can be cracked, however, it is a bit tricky and too hard
for "normal" users. Again, this pwd is harder to crack than sheet or
workbook passwords, there's no qick to find download for it but it CAN
be cracked easily if sbd. wants to.

arno

Many thanks for this simple solution Arno,

Regards
Ray.

"arno" wrote:

> Hi Ray,
>
> > The macro itself works fine; my problem lies in the fact that all
> > users can open the macro if they so desire and view the password,
>
> set a vba-project password, users will not be able to open and see your
> macros.
>
> Set the pwd in VBA-Editor/Extras/PropertiesofVBAProject/Protection -
> hide the project and enter a password.
>
> This password can be cracked, however, it is a bit tricky and too hard
> for "normal" users. Again, this pwd is harder to crack than sheet or
> workbook passwords, there's no qick to find download for it but it CAN
> be cracked easily if sbd. wants to.
>
> arno
>
>

Another approach but with more involved:

Put your macros in an add-in. Protect the code. Than teach your user how
to load an add-in.

Some one else may have a way to automate loading an add-in.

--
rand451
"Ray Green" <[email protected]> wrote in message
news:[email protected]...
> Many thanks for this simple solution Arno,
>
> Regards
> Ray.
>
> "arno" wrote:
>
>> Hi Ray,
>>
>> > The macro itself works fine; my problem lies in the fact that all
>> > users can open the macro if they so desire and view the password,
>>
>> set a vba-project password, users will not be able to open and see your
>> macros.
>>
>> Set the pwd in VBA-Editor/Extras/PropertiesofVBAProject/Protection -
>> hide the project and enter a password.
>>
>> This password can be cracked, however, it is a bit tricky and too hard
>> for "normal" users. Again, this pwd is harder to crack than sheet or
>> workbook passwords, there's no qick to find download for it but it CAN
>> be cracked easily if sbd. wants to.
>>
>> arno
>>
>>