MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting cells protected by a macro.


Posted by Rich Finn on May 05, 2000 8:05 AM

Hi,
I'm trying to protect some cells in a spreadsheet, whilst allowing the user to be able to resize & sort the spreadsheet.

I came across the 'Forbid' macro that doesn't allow a user to select certain cells. Does anyone know how to change this, so that they can select these cells but anything they enter is discarded?

Thanks,
Rich.


Posted by Ivan Moala on May 05, 2000 2:00 PM

Hi Rich
What is the Forbid macro?
I can only assume it is driven by the sheet selection change event.
If this is so then you can deactivate this.
Could you please elaborate on the code.


Ivan

Posted by Rich Finn on May 08, 2000 6:44 AM

You're right it is driven by the SelectionChange event. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Set Range1 = Selection
Set Range2 = Range("Forbidden")
Set Range3 = Union(Range1, Range2)

If Range3.Areas.Count = 1 Then
ReturnCell.Select
Else
Set ReturnCell = Selection
End If

End Sub

That said, I managed to get what I wanted using the OnEntry Event. Here's a rough example:

e.g.

Sub Auto_open()
Application.OnEntry = "checkit"
End Sub

Sub checkit()
If Application.Caller.Column >= 1 and Application.Caller.Column <= 4 then
Dummy = MsgBox("Cells Protected")
Application.Undo
End if