Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 29th 11, 05:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2011
Posts: 3
Default 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   Report Post  
Old September 29th 11, 06:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 86
Default 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   Report Post  
Old September 29th 11, 06:19 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default 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   Report Post  
Old September 30th 11, 04:45 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2011
Posts: 3
Default 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   Report Post  
Old September 30th 11, 08:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1,549
Default 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   Report Post  
Old October 2nd 11, 04:59 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2011
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Data Protection Best Practice: AKA: Real Sheet Protection Mushman(Woof!)[_2_] Excel Discussion (Misc queries) 4 December 30th 09 02:20 AM
Excel Data Protection- AKA: Sheet/Macro Password Protection Mushman(Woof!) Setting up and Configuration of Excel 0 December 29th 09 07:50 AM
Sheet protection Niall 84 Excel Discussion (Misc queries) 0 July 30th 08 03:06 PM
Sheet Protection OdAwG Excel Discussion (Misc queries) 2 April 12th 07 01:21 PM
Sheet Protection Narendra Excel Discussion (Misc queries) 1 February 2nd 07 04:16 PM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017