event macros - xl 2003

[Sue]

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

[Sue]

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

"Sue" wrote:

> 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?

[Sue]

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" wrote:

>
> 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
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=482553
>
>

[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 the first place:

ActiveSheet.Protect Password:="pass"

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

HTH

DominicB

[Sue]

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?

"dominicb" wrote:

>
> 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
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=482553
>
>

[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)


"Sue" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "dominicb" wrote:
>
>>
>> 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
>>
>>
>> --
>> dominicb
>> ------------------------------------------------------------------------
>> dominicb's Profile:
>> http://www.excelforum.com/member.php...o&userid=18932
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=482553
>>
>>

[Sue]

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

"dominicb" wrote:

>
> 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 the
> first place:
>
> ActiveSheet.Protect Password:="pass"
>
> Just remember that if you have other parameters set (eg
> DrawingObjects:=True, Contents:=True, Scenarios:=True etc) just
> remember to set your password apart from those with a comma.
>
> HTH
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=482553
>
>

[Sue]

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

"Peo Sjoblom" wrote:

> 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)
>
>
> "Sue" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > "dominicb" wrote:
> >
> >>
> >> 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
> >>
> >>
> >> --
> >> dominicb
> >> ------------------------------------------------------------------------
> >> dominicb's Profile:
> >> http://www.excelforum.com/member.php...o&userid=18932
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=482553
> >>
> >>
>
>