Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a com
Hi,
How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a com
For a SINGLE sheet that is unProtected:
Sub Macro1() With ActiveSheet .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True .EnableSelection = xlUnlockedCells End With End Sub will: 1. Protect the sheet 2. allow unlocked cells to receive comments 3. not allow locked cells to receive comments Insert in a loop -- Gary''s Student - gsnu200738 "Prashanth KR" wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a com
Sub Protect()
Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a
Hello Gary, It was really very helpful. Thanks again. Hope to be in touch with you. My appreciations for the immediate response. Prashanth KR. "Gary''s Student" wrote: For a SINGLE sheet that is unProtected: Sub Macro1() With ActiveSheet .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True .EnableSelection = xlUnlockedCells End With End Sub will: 1. Protect the sheet 2. allow unlocked cells to receive comments 3. not allow locked cells to receive comments Insert in a loop -- Gary''s Student - gsnu200738 "Prashanth KR" wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a Com
Wow.... this worked wonderful. And this is what I was looking at. Thanks a lot Gord. Iam totally impressed and saved me a lot of time as I had more than 50 sheets. My appreciations for your immediate response. Prashanth KR. "Gord Dibben" wrote: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a
Wow Gord .... this is what I was looking for. It worked perfectly. Thanks again, My sincere appreciations for your immediate response. Prashanth KR. "Gord Dibben" wrote: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a Com
You're welcome.............both times<g
Gord On Wed, 22 Aug 2007 02:02:01 -0700, Prashanth KR wrote: Wow.... this worked wonderful. And this is what I was looking at. Thanks a lot Gord. Iam totally impressed and saved me a lot of time as I had more than 50 sheets. My appreciations for your immediate response. Prashanth KR. "Gord Dibben" wrote: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a
Hi Gord, Iam encountering another problem. I tried, to unprotect the sheets with the following Macro to make some modifications: Sub UnProtect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws ..UnProtect Password:="Protect" ..EnableSelection = xlUnlockedCells End With Next ws End Sub But, when I try again, to protect the sheets with your macro, it does protects all sheets, but it does not allow me to "Select the Locked Cells". Kindly help. Thanks in advance, Prashanth KR. "Gord Dibben" wrote: You're welcome.............both times<g Gord On Wed, 22 Aug 2007 02:02:01 -0700, Prashanth KR wrote: Wow.... this worked wonderful. And this is what I was looking at. Thanks a lot Gord. Iam totally impressed and saved me a lot of time as I had more than 50 sheets. My appreciations for your immediate response. Prashanth KR. "Gord Dibben" wrote: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I protect a number of sheets allowing the user to add a
but it does not allow me to "Select the Locked Cells".
The code does its job. Not allowing you to select locked cells, only unlocked cells. If you want to select locked and unlocked cells remove the line ..EnableSelection = xlUnlockedCells Gord On Tue, 28 Aug 2007 04:40:01 -0700, Prashanth KR wrote: Hi Gord, Iam encountering another problem. I tried, to unprotect the sheets with the following Macro to make some modifications: Sub UnProtect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .UnProtect Password:="Protect" .EnableSelection = xlUnlockedCells End With Next ws End Sub But, when I try again, to protect the sheets with your macro, it does protects all sheets, but it does not allow me to "Select the Locked Cells". Kindly help. Thanks in advance, Prashanth KR. "Gord Dibben" wrote: You're welcome.............both times<g Gord On Wed, 22 Aug 2007 02:02:01 -0700, Prashanth KR wrote: Wow.... this worked wonderful. And this is what I was looking at. Thanks a lot Gord. Iam totally impressed and saved me a lot of time as I had more than 50 sheets. My appreciations for your immediate response. Prashanth KR. "Gord Dibben" wrote: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws .Protect Password:="Protect", DrawingObjects:=False, Contents:=True .EnableSelection = xlUnlockedCells End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 21 Aug 2007 09:20:01 -0700, Prashanth KR wrote: Hi, How do I protect a workbook having a number of sheets (around 50) allowing the user to add a comment. I tried this with the following macro: Sub Protect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="Protect" Next ws End Sub But it would not allow me to add a "Comment" in the unprotected cells. Kindly help me to create a macro so that I can protect all sheets at one shot and also allow users to add comments in the unprotected cells. Please also note that Iam very new to VBA. Your help is much appreciated, Prashanth KR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allowing user to merge cells | Excel Discussion (Misc queries) | |||
Allowing data entry in protected sheets | Excel Discussion (Misc queries) | |||
Unshared workbook allowing write access to more than 1 user | Excel Discussion (Misc queries) | |||
To Protect Excel documents and allowing only few cells to edit | Excel Worksheet Functions | |||
How do I protect my comments from being edited by another user? | Excel Discussion (Misc queries) |