You are here:

Excel/Comments on protected sheet

Advertisement


Question
Hello Garreth

Another question..

I have a worksheet where several individuals assign a value to a cell.

Each individual (say 5) each have their own cell in which they make their entry.

The worksheet is protected to avoid inadvertent changes to other cells.

The individual cells (columns) assigned to each individual are also protected but can be accessed when the individual provides a password in order to make their selection. (ALLOW USERS TO EDIT RANGES)

I also want the individual to be able to enter comments into the cell to which they make their selection.

I have selected “EDIT OBJECTS” when protecting the sheet and it does do as I would like but the issue is I entering comments is not limited to the individuals column and that they can enter comments in any cell.

Is there a way I can protect all the cells from changes but when a password they can make changes to their column and enter comments to their column only?

Thank you.

Gord

PS Can give you a sample WorkSheet

Answer
Good day Gord,

Apologies for the late reply,

Paste this code below into your VBE editor :
Note: You can amend this code to suit your stats. If you have problems dong this please ask me again.

From Excel:  Press  Alt+F11 (This will take you to the VBE Screen)
Select from the Menu : Insert : Module

Append the code :
'Code Begins
Option Explicit
Sub unprot()
If Application.UserName = "EB" Then
ActiveSheet.Unprotect Password:="sheetpassword"
Range("C:C").Locked = False
ActiveSheet.Protect Password:="sheetpassword"
End If
   If Application.UserName = "MM" Then
   ActiveSheet.Unprotect Password:="sheetpassword"
   Range("D:D").Locked = False
   ActiveSheet.Protect Password:="sheetpassword"
   End If
       If Application.UserName = "GH" Then
       ActiveSheet.Unprotect Password:="sheetpassword"
       Range("E:E").Locked = False
       ActiveSheet.Protect Password:="sheetpassword"
       End If
If Application.UserName = "JT" Then
ActiveSheet.Unprotect Password:="sheetpassword"
Range("F:F").Locked = False
ActiveSheet.Protect Password:="sheetpassword"
End If
   If Application.UserName = "DC" Then
   ActiveSheet.Unprotect Password:="sheetpassword"
   Range("G:G").Locked = False
   ActiveSheet.Protect Password:="sheetpassword"
   End If
       If Application.UserName = "MR" Then
       ActiveSheet.Unprotect Password:="sheetpassword"
       Range("H:H").Locked = False
       ActiveSheet.Protect Password:="sheetpassword"
       End If
End Sub

'End of Code

Hope this helps you.

Regards
Garreth
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Garreth

Expertise

Anything relating to Excel formulae and VBA I can answer Unfortunately dont know COBOL

Experience

Over 15 years of VBA

Organizations
Belong to no bodies purce but I help where I can

Education/Credentials
University of Cape Town (Computer Science Degree)

©2016 About.com. All rights reserved.