locking cells in Excel

[Yvonne]

Hi
I am clueless when it comes to VBA but found the code below on the MrExcel archives. It works perfectly which is great, but I've got more than one cell to protect, and when I try to cut and paste and change the cell references, it tells me that I've got an ambiguous name - I assume because it's called the same as the first code, but I don't know how to change this without making it not work... can anyone help, without being too tecnical!!!?

thanks

Yvonne

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim WatchCell As Range

Set WatchCell = Range("B4")
On Error GoTo ResetEvents

If Not Intersect(Target, WatchCell) Is Nothing Then
Application.EnableEvents = False
Range("C4").Select
End If

ResetEvents:
Set WatchCell = Nothing
Application.EnableEvents = True

End Sub

 

[lenze]

Hi and welcome to the board!!

Is there more to your code than what you posted? Where does the lock cells come in? What exactly do you want to do?

lenze

 

[Yvonne]

Hi Lenze,
Thanks for your help!. The original code I copied and pasted was below, but I messed around with it and removed a little bit as I didn't want a message box popping up. It does work as I first pasted it though, without any error message. Basically I am creating a form for users to fill in and I just want to ensure that users do not not to overtype my titles eg name etc (which are in B4), but are automatically directed to the cell 'next door' ie C4. ... and then again for B5 into C5 etc etc

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim WatchCell As Range

Set WatchCell = Range("A1")
On Error GoTo ResetEvents

If Not Intersect(Target, WatchCell) Is Nothing Then
Application.EnableEvents = False
Range("A2").Select
MsgBox "You cannot select cell A1", vbCritical
End If

ResetEvents:
Set WatchCell = Nothing
Application.EnableEvents = True

End Sub

 

[lenze]

Yvonne;
I am still not clear what you are really trying to do. Let's step back and start anew. Forget the code you have for the moment. Can you tell us exactly what you are trying to do and what steps you or the user are doing. Be specific as to how and where data is entered. I'm guessing you want to prohibit certain cells from being selected or changed. If so, simply Select ALL of those cells (Use the CTRL key to select non-contiguous cells) and then assign the selection a name (say NoNoCells). Then something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("NoNoCells")) Is Nothing Then Exit Sub
    Target.Offset(0, 1).Select
End Sub

This is just a guess at your requirements. When you provide the specifics, we can be more precise.

lenze

 

[JimH]

Lenze,

I have a similar situation. I have three cells (K2:K4) that I want to restrict access to. How do I modify your code to accomplish this?

Thanks.

 

[lenze]

Hi Jim: Sorry I've taken so long to get back to you. I've been busy fishing. The code, however, is really the same. In your case, I would use a Selection change envent or a worksheet change event. A worksheet Seletion change would look like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect (Target,Range("K2:K4") Is Nothing Then Exit Sub
Range("$A$1").Select
End Sub