Home |
Search |
Today's Posts |
#1
|
|||
|
|||
event macros - xl 2003
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? |
#2
|
|||
|
|||
event macros - xl 2003
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 |
#3
|
|||
|
|||
event macros - xl 2003
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? |
#4
|
|||
|
|||
event macros - xl 2003
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 |
#5
|
|||
|
|||
event macros - xl 2003
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 |
#6
|
|||
|
|||
event macros - xl 2003
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 |
#7
|
|||
|
|||
event macros - xl 2003
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" wrote in message ... 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 |
#8
|
|||
|
|||
event macros - xl 2003
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 |
#9
|
|||
|
|||
event macros - xl 2003
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How copy Excel macros from Excel XP (or before) to Excel 2003? | Excel Worksheet Functions | |||
Macros in 2003 | Excel Discussion (Misc queries) | |||
Excel 97 macros to Excel 2003 | Excel Worksheet Functions | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) | |||
can a workbook with macros created in excel 2003 work in excel 20. | Excel Discussion (Misc queries) |