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

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

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

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

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

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