| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Password protect parts of worksheet
I have macros on a spreadsheet/worksheet that does some calculations.
I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Password protect parts of worksheet
Hi ddwebb
You can unprotect your sheet in the code and protect it again after your code is ready. But I like this way. Protect your worksheets with code like this Copy this in the Thisworkbook module. The macro's will be working now Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ABCD", , , userinterfaceonly:=True Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... I have macros on a spreadsheet/worksheet that does some calculations. I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Password protect parts of worksheet
Do I have to mention the name of the worksheet or the columns I want
protected? For a dude that is not a programmer, I am muddling thru this and trying to learn. What is ABCD, is that a value I need to change? Or ThisWorkBook? dougw "Ron de Bruin" wrote: Hi ddwebb You can unprotect your sheet in the code and protect it again after your code is ready. But I like this way. Protect your worksheets with code like this Copy this in the Thisworkbook module. The macro's will be working now Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ABCD", , , userinterfaceonly:=True Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... I have macros on a spreadsheet/worksheet that does some calculations. I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Password protect parts of worksheet
Names: My worksheet name is: CheckIn
Columns want protected: L, T, U, and V thx dw "ddwebb" wrote: Do I have to mention the name of the worksheet or the columns I want protected? For a dude that is not a programmer, I am muddling thru this and trying to learn. What is ABCD, is that a value I need to change? Or ThisWorkBook? dougw "Ron de Bruin" wrote: Hi ddwebb You can unprotect your sheet in the code and protect it again after your code is ready. But I like this way. Protect your worksheets with code like this Copy this in the Thisworkbook module. The macro's will be working now Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ABCD", , , userinterfaceonly:=True Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... I have macros on a spreadsheet/worksheet that does some calculations. I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Password protect parts of worksheet
Hi
You lock/unlock the cells you want the normal way. This event will protect all worksheets for you in the workbook. It use userinterfaceonly in the protect line to keep your macro's working. ABCD = the password of the worksheets(you can change it For a dude that is not a programmer, I am muddling thru this Try it first on a test workbook Right click on the Excel icon next to File in the Worksheet menu bar Choose view code Paste this event there Alt-q to go back to Excel Save/close/reopen the workbook -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... Do I have to mention the name of the worksheet or the columns I want protected? For a dude that is not a programmer, I am muddling thru this and trying to learn. What is ABCD, is that a value I need to change? Or ThisWorkBook? dougw "Ron de Bruin" wrote: Hi ddwebb You can unprotect your sheet in the code and protect it again after your code is ready. But I like this way. Protect your worksheets with code like this Copy this in the Thisworkbook module. The macro's will be working now Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ABCD", , , userinterfaceonly:=True Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... I have macros on a spreadsheet/worksheet that does some calculations. I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Password protect parts of worksheet
Thanks. That seems to be working.
Now to put into production and let the staff find a problem. dw "Ron de Bruin" wrote: Hi You lock/unlock the cells you want the normal way. This event will protect all worksheets for you in the workbook. It use userinterfaceonly in the protect line to keep your macro's working. ABCD = the password of the worksheets(you can change it For a dude that is not a programmer, I am muddling thru this Try it first on a test workbook Right click on the Excel icon next to File in the Worksheet menu bar Choose view code Paste this event there Alt-q to go back to Excel Save/close/reopen the workbook -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... Do I have to mention the name of the worksheet or the columns I want protected? For a dude that is not a programmer, I am muddling thru this and trying to learn. What is ABCD, is that a value I need to change? Or ThisWorkBook? dougw "Ron de Bruin" wrote: Hi ddwebb You can unprotect your sheet in the code and protect it again after your code is ready. But I like this way. Protect your worksheets with code like this Copy this in the Thisworkbook module. The macro's will be working now Private Sub Workbook_Open() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ABCD", , , userinterfaceonly:=True Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ddwebb" wrote in message ... I have macros on a spreadsheet/worksheet that does some calculations. I have protected certain columns that have formulas and that seems to be ok until I want to press my macro button and have it do additional calculations or print. But since I have the worksheet protected, how can I also 'unprotect' the macro button to all this? i.e. Column E is protected and I set the Tools/Protection/worksheet password protected. I have several macro buttons that does various things. When I click on these buttons, I get an error stating that they can not be run because the worksheet is protected. How can I protect my formulas and allow the macro buttons to work? or thx dwebb |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a way that I can password protect a worksheet | Excel Worksheet Functions | |||
| Password Protect a Worksheet | Excel Discussion (Misc queries) | |||
| Protect a worksheet with a password | Excel Worksheet Functions | |||
| password protect worksheet | Excel Discussion (Misc queries) | |||
| Password Protect Worksheet Viewing | Excel Programming | |||
![ddwebb[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
