Password Setting

[VBALearner]

hi hi, it is so nice to have this forum...

I have a query on VBA programming regarding password setting. How to
set up a password when opening a file. good to have expiry date!! I
tried once before to write a macro to lock the file. However, if the
user choose "disable macros", then it does not work! Is there any way
out?? I know there is a built-in funtion in Excel to protect a file
from reading or writing upon opening. But there is no expiry date
attached!! thank you for yr help!!!

 

[Vasant Nanavati]

However, if the
user choose "disable macros", then it does not work! Is there any way
out??

Not really.

 

[Piranha]

VBALearner,
You could force the user to enable macros, by having the workbook auto
close
if the macros are not enabled..

Here is one way.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

 

[Gareth]

Just expand on this, you can't have the workbook auto close since this
would require the running of macros. What you can do is have the
workbook default to a blank sheet upon opening (maybe with a message
advising to reopen with macros enabled) and all the other sheets hidden.

I checked our the below link. In addition to the code it proposes, I
would use BeforeSave to hide all the sheets upon saving. Otherwise, if
the user saves but doesn't close the workbook it will not be saved in
the "locked up" state should they crash out of Excel.

With respect to your other query about password dates: no such
functionality exists to my knowledge. There are ways and means around
such a thing though. For example, you could include a check for the
system date in the BeforeSave event of a workbook and remove / add a
password depending on the date.

My preferred option, rather than use event trapping, is to
programmatically open the workbook - maybe from a option form or menu
button loaded by a separate AddIn - often using the user's network login
to determine their rights. This way you are more in control and it's
often simpler for the user too - but it doesn't lend itself to all
circumstances.

HTH,
Gareth

 

[Piranha]

Gareth,
Yeah i didn't express my self very well did i. (You could force th
user to enable macros,
by having the workbook auto close if the macros are not enabled..)
Pretty much common sense huh.

What i was thinking and what i wrote weren't coordinated

I have a few workbooks set up like you describe, they work well.

Thanks for correcting my post, don't want to give bad info.

 

[VBALearner]

Dear Dave,Gareth,Piranha & Vasant

Thank you so much for yr advice or info. It helps me a lot....

 

[Gareth]

I couldn't agree more - getting down what's in your head just doesn't
happen sometimes - even when you read it back to yourself it looks
ok.... until you've posted it that is... *then* you realise!

;-)