Allow copying of all attributes of protected cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that has several auto-calculated columns that I would like to protect to prevent users from entering values. On the other hand, users should be able to copy the last row to create additional rows for entries. When I protect the sheet and copy the row, all the formulas turn into fixed values and the data validation rules (selection dropdowns) disappear. I've tried not marking the cells with the formulas as "locked" in the cell format dialog, but it seems to make no difference. Any ideas would be appreciated.
 
You can use events to prevent users from selecting the column! Right-click the sheet tab, select View Code, enter a variation of this
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Select Case Target.Colum
Case 3, 8 To 12, 18 'columns users can't click int
Range("A1").Selec
MsgBox "No no no!!
End Selec
End Su


----- Mathias wrote: ----

I have a worksheet that has several auto-calculated columns that I would like to protect to prevent users from entering values. On the other hand, users should be able to copy the last row to create additional rows for entries. When I protect the sheet and copy the row, all the formulas turn into fixed values and the data validation rules (selection dropdowns) disappear. I've tried not marking the cells with the formulas as "locked" in the cell format dialog, but it seems to make no difference. Any ideas would be appreciated.
 
Thanks! Works great in preventing the selection without protecting the document.

The only problem is that now I can't select the row to copy it. Is there an event that would capture a value change (before it's executed, or a way to undo the value change) instead of capturing the selection change?
 
Back
Top