Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
Hello,
I have a problem protecting a worksheets. Basically there are several columns on this sheet where I enter date, employee ID number and it gives me other info about this employee in other columns retrieved from yet another sheet. The problem I have is that this workbook is used by other people and they often accidentally delete formulas I have in those other columns. So I wanted to protect the sheet leaving only those columns where we type in, unprotected. Everything works fine except that I also have VBA code which tells excel to keep the format of the sheet (for print purpose) the same when we update this sheet dally deleting some rows with Ctrl+D. So, protection works fine but when I try to Ctrl+D to delete a row excel won't let me. Is there any way to fix this problem? Appreciate any help Mark |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
This is usually overcome by using code to unprotect sheet, delete
chosen column(s) then re-protect. Sub test() With ActiveSheet .Unprotect Password:="mypword" Selection.EntireColumn.Delete .Protect Password:="mypword" End With End Sub Gord Dibben Microsoft Excel MVP On Thu, 29 Sep 2011 12:17:35 -0400, Mark Wolf wrote: Hello, I have a problem protecting a worksheets. Basically there are several columns on this sheet where I enter date, employee ID number and it gives me other info about this employee in other columns retrieved from yet another sheet. The problem I have is that this workbook is used by other people and they often accidentally delete formulas I have in those other columns. So I wanted to protect the sheet leaving only those columns where we type in, unprotected. Everything works fine except that I also have VBA code which tells excel to keep the format of the sheet (for print purpose) the same when we update this sheet dally deleting some rows with Ctrl+D. So, protection works fine but when I try to Ctrl+D to delete a row excel won't let me. Is there any way to fix this problem? Appreciate any help Mark |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
If I understand you correctly:
You have VBA code to delete rows and format cells on a worksheet. ... the code is executed by pressing the Ctrl+D keys. ... the code doesn't run, instead you get an Excel message to unprotect the sheet. To handle this issue, insert code at the start of the macro to unprotect the sheet and at the end of the macro insert code to protect the sheet... '--- Sub BreadcrumbWaggles ActiveSheet.Unprotect Password:="mydogsname" ' your code here ActiveSheet.Protect Password:="mydogsname" End Sub '--- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Rows Excel add-in: Color rows, Delete rows, Insert rows) "Mark Wolf" wrote in message ... Hello, I have a problem protecting a worksheets. Basically there are several columns on this sheet where I enter date, employee ID number and it gives me other info about this employee in other columns retrieved from yet another sheet. The problem I have is that this workbook is used by other people and they often accidentally delete formulas I have in those other columns. So I wanted to protect the sheet leaving only those columns where we type in, unprotected. Everything works fine except that I also have VBA code which tells excel to keep the format of the sheet (for print purpose) the same when we update this sheet dally deleting some rows with Ctrl+D. So, protection works fine but when I try to Ctrl+D to delete a row excel won't let me. Is there any way to fix this problem? Appreciate any help Mark |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
Thank you gentlemen,
Jim, you are correct, that's exactly what happens but I tried to modify VBA and it does not work. Here is the code: Sub MacroDelete() 'Ctrl-D Selection.EntireRow.Delete ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" End Sub Sub MacroInsert() 'Ctrl-I If ActiveSheet.Name = "Sick Sheet" Or ActiveSheet.Name = "Excused Sheet" Then Selection.EntireRow.Insert 'If ActiveSheet.Name = "Sick Sheet" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" 'Else ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" End If End Sub Sub PrintEntire() 'Ctrl-Q activeSheetName = Application.ActiveSheet.Name Sheets(Array("Sick Sheet", "Excused Sheet")).PrintOut Sheets(activeSheetName).Select End Sub This code is in the Module1 Thanks for your help Mark |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
Mark,
Please avoid the use of the term "does not work / doesn't work". Think of it as telling your auto mechanic "my car doesn't work". That said... What may be the problem is the shortcut keys are case sensitive. So to use Ctrl + D, I have to press Ctrl + Shift + D. I already covered the placement of the protect/unprotect code lines. They have to go in both the Delete and Insert subs. '--- Jim Cone Portland, Oregon USA http://excelusergroup.org/media/ (Formats & Styles xl add-in: lists/removes unused styles & number formats) - free "Mark Wolf" wrote in message ... Thank you gentlemen, Jim, you are correct, that's exactly what happens but I tried to modify VBA and it does not work. Here is the code: Sub MacroDelete() 'Ctrl-D Selection.EntireRow.Delete ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" End Sub Sub MacroInsert() 'Ctrl-I If ActiveSheet.Name = "Sick Sheet" Or ActiveSheet.Name = "Excused Sheet" Then Selection.EntireRow.Insert 'If ActiveSheet.Name = "Sick Sheet" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" 'Else ActiveSheet.PageSetup.PrintArea = "$A$1:$J$78" End If End Sub Sub PrintEntire() 'Ctrl-Q activeSheetName = Application.ActiveSheet.Name Sheets(Array("Sick Sheet", "Excused Sheet")).PrintOut Sheets(activeSheetName).Select End Sub This code is in the Module1 Thanks for your help Mark |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sheet Protection
Thank you Jim
Now the code runs properly. I put protect/unprotect code in the wrong place, then I realized it will not do anything then I figured out where it has to go. Sorry I'm new to excel programming. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
Sheet protection | Excel Discussion (Misc queries) | |||
Sheet Protection | Excel Discussion (Misc queries) | |||
Sheet Protection | Excel Discussion (Misc queries) |