MrExcel Publishing
Your One Stop for Excel Tips & Solutions

proctected cells.....any ideas..inside..


Posted by newby on April 08, 2000 10:17 PM

if we proctected our cells n people try 2 write
something on it..a warning message will appear
n said that that particular cells r proctected..
is there any codes that can prevent from this
warning message apppear..let's say if the user
try 2 write something..it's just don't work
but the warning message will not appear..
any ideas.thanks


Posted by Celia on April 09, 2000 8:14 AM


newby
This can be done with a worksheet level event procedure. I adapted some code from AB’s site (http://geocities.com/aaronblood/ExamplesVBA.html) which provides an example called ForbidRange.

For example, if you wanted to prevent users from being able to select or edit cells B1:B6,C2,and E3, the following code would do it. If the user tried to select any of these cells, he would be unable to do so, and cell A1 would become the active cell instead.
Celia

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Range1 = Selection
Set Range2 = Range("B1:B6,C2,E3")
Set Range3 = Intersect(Range1, Range2)
If Not Range3 Is Nothing Then
Range("A1").Select
End If
End Sub

Posted by newby on April 09, 2000 11:31 PM

thanks celia but the problem is......

what i want to do is like this :

let say cell A1, B1, so the answer is in cell C1
i mean the formula is in C1(=SUM A1:B2)..what i dream 2 do is
when user try to delete the formula in C1, nothing
happened..the common thing to do when u type
in a protected cells, a warning message will appear
this is i want to do..avoid the warning message
to appear when user try to modified the protected
but they can't modified it....is it possible????thanks again

Posted by Celia on April 10, 2000 1:59 AM

Re: thanks celia but the problem is......

newby
I don’t know how to turn-off the warning (or whether or not it’s possible). Perhaps someone knows how to do this.
However, you can do what you want for the example you have given by putting “C1” in the code instead of the cells I used in my example :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Range1 = Selection
Set Range2 = Range("C1")
Set Range3 = Intersect(Range1, Range2)
If Not Range3 Is Nothing Then
Range("A1").Select
End If
End Sub

Copy the above code and paste it to the Worksheet module (right click the worksheet tab, select View Code, and the code module will appear). You do not have to protect cell C1
An improved version of the code is as follows (but the above is OK for what you need) :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static ReturnCell As Range
Dim Range1 As Range, Range2 As Range, Range3 As Range
Set Range1 = Selection
Set Range2 = Range("C3")
Set Range3 = Intersect(Range1, Range2)
If Not Range3 Is Nothing Then
On Error GoTo Retreat
ReturnCell.Select
On Error GoTo 0
Else
Set ReturnCell = Selection
End If
Exit Sub
Retreat:
Range("A1").Select
Exit Sub
End Sub

Celia