event macros - xl 2003

[Guest]

I would like a macro to unprotect a worksheet, sort, reprotect the worksheet
and then save, all upon clicking on the worksheet when first opening it.
I have recorded a macro that does all of that except it has to be run
manually, and also for some reason it will not unprotect without the password
being keyed in while the macro is running nor will it reprotect with the
password. It reprotects it with no password entered. The password was
entered during the recording of the macro. Apart from this glitch, I would
like some help with getting this macro to become an event macro by triggering
it with the opening of the worksheet.
Problem No. 1: can anyone help with the password problem of my macro?
Problem No. 2: can anyone help with the event portion of the macro?

 

[dominicb]

Good morning Sue

Problem 1:
Use of the password property as shown below will solve that problem.
ActiveSheet.Unprotect Password:="pass"

Problem 2:
Renaming your macro auto_open will cause it to run automatically on the
file being opened.

If you prefer you can set it up as an event procedure - in this case
rename your macro Private Sub Workbook_Open() and move it from the
modules part of the VBE to ThisWorkbook.

HTH

DominicB

 

[Guest]

Hi,
Back again.
I have now got the event macro running but still need an answer on the
password problem.
The worksheet password is not to be made available to other users. So I
need to have the macro do the password and not to rely on it manually being
input in the box that comes up, as the user will not know it and I don't want
them to know it. If they can unprotect the worksheet they may lose formulas
etc.
Thanks
Sue

 

[Guest]

Hi dominicb,
Thank you for your response. I have the event macro running well now plus
based on your advice I don't have to enter the password now while the macro
runs to unprotect the worksheet but... when the macro reprotects the
worksheet after the sort it is not passwording it. The worksheet is
protected but anybody could unprotect it again without the password. How do
I get it to reset the password?
Thanks heaps
Sue

 

[dominicb]

Hi Sue

Whoops! Never thought to mention that bit to you ...! Fortunately
it's quite easy and based on the same syntax as unprotecting it in th
first place:

ActiveSheet.Protect Password:="pass"

Just remember that if you have other parameters set (e
DrawingObjects:=True, Contents:=True, Scenarios:=True etc) jus
remember to set your password apart from those with a comma.

HTH

Dominic

 

[Guest]

Hi dominicb,
Sue again.
Have got the password sorted so no problems there thanks to you. However, I
am concerned that if someone right clicks on the worksheet tab and clicks
view code, that they will see the password. How do I hide it so it can't be
seen?

 

[Peo Sjoblom]

Right click the sheet in the VBE and select properties, click the protection
tab (rest should be self explanatory)
Won't help against someone that is determined enough to Google on how to
crack the protection but probably for the
average user

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Hi dominicb,
Thanks for your help. I am up and running thanks to you and I worked out
the same logic and the comma part but I'm please you confirmed it.
Sue

 

[Guest]

Hi Peo,
Thanks alot. I followed my nose and it is all working great.
Doing a great job, really appreciate it.
Sue