Protect validated columns

[excel5111987]

Hi,

In a excel2007 worksheet I have applied validation in 4-5 columns. And I want to protect only those 4-5 column but not the whole sheet.

I have tried these, but it is not working

1) selected single column, formate menue, uncheck hide and protect.
2) Selected single column again , formate menue, check hide and protect, review and protect sheet,

But doing so, protects my whole sheet rather than protecting selected columns.

Please help

 

[Trevor G]

Try this.

Select Columns (D:E) and then use Ctrl + 1 (Format Cells) then select protection Tab then deselect Locked, then click OK, keep highlighted then select Review and Protect Sheet.

If this doesn't work you must have something else going on.

 

[excel5111987]

Dear Sir,

What exactly I want is that Allow users to select list form validation columns which is password protected.

Say I have Column A in which I have applied validation with few list. What they do, they remove my validation everyday. That is way I have protected those column, but now they even can not select list from validation.

So, I have protected those columns. Now I need the way so that they can not remove validation but they can only select list from validation.

 

[Trevor G]

Take a look at this link, it gives you code which has to go into the Workbook Event which will stop any row, column from being deleted.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=660

There is an example workbook which you can explore.

Code used is in the Workbook Events, so you can test it on a blank workbook and use Alt + F11 then double click on the left where it states thisWorkBook and paste in the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ((Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address)) Then

With Application
.EnableEvents = False
.Undo
MsgBox "No deleting rows or columns", 16
.EnableEvents = True
End With

Else

Exit Sub

End If
End Sub

 

[Peter_SSs]

Take a look at this link, it gives you code which has to go into the Workbook Event which will stop any row, column from being deleted.

That wouldn't stop the user from wrecking the Data Validation by pasting into the data-validated cell.