Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
Can I protect a sheet such that users cannot delete or edit or view it?
Thanks in Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
Not really.
Any suggestion that you get would rely on worksheet and workbook protection and those are pretty easy to break. So if you don't want others to see the data, don't share that data with them in excel. sam wrote: Can I protect a sheet such that users cannot delete or edit or view it? Thanks in Advance -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
With a little bit of VBA you can make the sheet xlVeryHidden which means it cannot be found or unhidden using the toolbar it can only be made visible via VBA again, however if you protect the worksheet it cannot be edited (well locked cells cannot) and if you further protect the workbook structure the sheet cannot be deleted, anyway in a VBA standard module add and run this (dont forget to change MySheet for your actual sheet name. Code: -------------------- Sub HideMySheet() Sheets("MySheet").Visible=xlVeryHidden End Sub -------------------- *How to add and run a Macro*1. *Copy* the macro above pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. sam;418173 Wrote: Can I protect a sheet such that users cannot delete or edit or view it? Thanks in Advance -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116359 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
Thanks for your help, In my situation.. I take user inputs from sheet1
through a userform, and those user inputs are stored in sheet2. So I need them to access Sheet1 only and not sheet2. Hence I want sheet2 to be protected such that it cannot be edited, viewed or deleted by these users but me. "Simon Lloyd" wrote: With a little bit of VBA you can make the sheet xlVeryHidden which means it cannot be found or unhidden using the toolbar it can only be made visible via VBA again, however if you protect the worksheet it cannot be edited (well locked cells cannot) and if you further protect the workbook structure the sheet cannot be deleted, anyway in a VBA standard module add and run this (dont forget to change MySheet for your actual sheet name. Code: -------------------- Sub HideMySheet() Sheets("MySheet").Visible=xlVeryHidden End Sub -------------------- *How to add and run a Macro*1. *Copy* the macro above pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. sam;418173 Wrote: Can I protect a sheet such that users cannot delete or edit or view it? Thanks in Advance -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116359 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
Then the xlVeryHidden will suit your situation sam;418303 Wrote: Thanks for your help, In my situation.. I take user inputs from sheet1 through a userform, and those user inputs are stored in sheet2. So I need them to access Sheet1 only and not sheet2. Hence I want sheet2 to be protected such that it cannot be edited, viewed or deleted by these users but me. "Simon Lloyd" wrote: With a little bit of VBA you can make the sheet xlVeryHidden which means it cannot be found or unhidden using the toolbar it can only be made visible via VBA again, however if you protect the worksheet it cannot be edited (well locked cells cannot) and if you further protect the workbook structure the sheet cannot be deleted, anyway in a VBA standard module add and run this (dont forget to change MySheet for your actual sheet name. Code: -------------------- Sub HideMySheet() Sheets("MySheet").Visible=xlVeryHidden End Sub -------------------- *How to add and run a Macro*1. *Copy* the macro above pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. sam;418173 Wrote: Can I protect a sheet such that users cannot delete or edit or view it? Thanks in Advance -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'protect a sheet such that it cannot be deleted - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=116359) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116359 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect a sheet such that it cannot be deleted
You can make the sheet visible via the properties window (in the VBE) for that
sheet. You'd want to protect the workbook project, too. But this workbook protection is easily broken and code to unhide the sheet could exist in a different workbook's project. Simon Lloyd wrote: With a little bit of VBA you can make the sheet xlVeryHidden which means it cannot be found or unhidden using the toolbar it can only be made visible via VBA again, however if you protect the worksheet it cannot be edited (well locked cells cannot) and if you further protect the workbook structure the sheet cannot be deleted, anyway in a VBA standard module add and run this (dont forget to change MySheet for your actual sheet name. Code: -------------------- Sub HideMySheet() Sheets("MySheet").Visible=xlVeryHidden End Sub -------------------- *How to add and run a Macro*1. *Copy* the macro above pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. sam;418173 Wrote: Can I protect a sheet such that users cannot delete or edit or view it? Thanks in Advance -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116359 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I protect a sheet so the rows/columns can't be deleted? | Excel Worksheet Functions | |||
How do I protect a whole folder from getting deleted??? | Excel Discussion (Misc queries) | |||
Protect one tab from being deleted | Excel Discussion (Misc queries) | |||
How do I protect formulas from being deleted? | Excel Worksheet Functions | |||
protect a workbook from being deleted | Excel Discussion (Misc queries) |