Lock cells without proctect the worksheet

[tina20]

Hi all,

In my worksheet, I have a "Item Name" column and I send that worksheet to the users to enter other information like price, quantity on hand, comments, etc...
I don't want the users change the name of these Items. The problem is when I protect this worksheet, the users can not change the format of other columns too, for example, they can not the width of the columns.

Do you have any idea to solve my problem? Thanks.

 

[Marcs41]

First, select your entire page. Right clik, Format cells - Protection.
UNcheck locked cells.
Next, select the cells you want to lock.
Same again, Right clik, Format cells - Protection.
CHECK locked cells .
Next, tools - protection - protect sheet.
Select all the things they are allowed to do (NOT the first one!).
Tha's it. The solution is out there.

 

[tina20]

Thanks for your suggestion, however, it does not work in my case.
I just want to lock the "Item Name" column because I use the VLOOKUP to link it to other worksheet. That means, the users can change other columns: they can change the size of columns, they can add information to other columns and they can sort all column; (but not change the "Item Name" column).

When I applied your suggestion, the users could not change the size of colums and they can not sort this worksheet.

Thanks for your help.

 

[Zathras]

This is very "quick and dirty" but you may be able to build on the concept:

In the VBA editor, double click on the sheet name then select "Worksheet" as the category of code in the upper left-hand combo box. Then paste in the following:

========================================================

Option Explicit
Dim SaveItemName As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ItemNameColumn As Integer
Dim DisableCell As String

ItemNameColumn = 2
DisableCell = "A1"

If Range(DisableCell).Value = "" Then
If Target.Column = ItemNameColumn Then
Target.Value = SaveItemName
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveItemName = Target.Value
End Sub

========================================================

Set "ItemNameColumn" to the column number of the column containing the descriptions (I used 2 for column "B" for testing.) Set "DisableCell" to a cell that is normally blank, but you can temporarily put a value in to disable the auto-repair feature of the Sub.

It won't prevent the user from changing the cell contents, but it will force it back to the original (unless the "DisableCell" is non-blank - this is to allow you to make changes when necessary without fiddling the subs.)

 

[Marcs41]

tina, if you want the users to change the other collums, you have to check the appropriate checkbox when protecting.
If for instance you select the entire sheet, (clicking on the little box top left) right click - format - UNLOCK everything
Next you select let's say Colum1, lock it.
When you then got to prtoect, check all boxes except the first. Then they can change width etc on everything except columm1 The solution is out there.

 

[rmikesmith]

marcs41,

I believe the problem Tina is having is due to the version of Excel she's using. Excel 97 and 2000 do not offer the options found in XP. Sheet protection is pretty much all or nothing.

tina20,

It looks like Zathras' code will do the trick, but here is a variation that "prevents" the user from selecting the forbidden cells prior to making any changes (it doesn't actually prevent selection, it just displays a message to the user then returns to the previously selected cell). Follow zathras' instructions to get to the Worksheet code module.

Option Explicit

Dim SaveCellAddress As String

Private Sub Worksheet_Activate()
  SaveCellAddress = ActiveCell.Address
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

  If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
    MsgBox "This cell cannot be selected/changed.", vbExclamation + vbOKOnly, "Input Error"
    Range(SaveCellAddress).Select
  Else
    SaveCellAddress = Target.Address
  End If
End Sub

HTH
Mike