PDA

View Full Version : Solved: Change event in range selection



Digita
07-24-2007, 06:45 PM
Howdy,

The following code is to lock any cell in a sheet once data has been input. For any blank cell, data input is allowed:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target = "" Then
Me.Unprotect ("password")
Target.Locked = False
Me.Protect ("password")
Else
Me.Unprotect ("password")
Target.Locked = True
Me.Protect ("password")
End If
End Sub


The problem I've got is that the code does not work properly if say I use the mouse to select a range of more than 1 cell. For example, if A1 & A2 is selected, I can type a new value over an existing value in A1.

Any idea on how to overcome this? Or can we disable range selection to ensure only 1 cell is input at a time?

Your input will be greatly appreciated.

Kind regards


KP

geekgirlau
07-24-2007, 07:23 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count = 1 Then
If Target = "" Then
Me.Unprotect ("password")
Target.Locked = False
Me.Protect ("password")
Else
Me.Unprotect ("password")
Target.Locked = True
Me.Protect ("password")
End If
Else
MsgBox "Please select ONE cell for data entry", vbInformation
ActiveCell.Select
End If
End Sub


Just keep in mind this might get irritating if you are trying to make modifications or copy a range etc.

Digita
07-24-2007, 08:50 PM
Thanks Geekgirlau. :hi: