How do I disable cell editing in vba?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DRICE - 28 Mar 2008 21:13 GMT

I am trying to programmaticly (vba) prevent users from editing any cell in
one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Mike H - 28 Mar 2008 21:38 GMT

Hi,

Right click the sheet tab, view code and paste this in. 3=Column C so change
to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
MsgBox "keep out of there"
Target.Offset(0, 1).Select
End If
End Sub

A word of caution, this fails completely if the user doesn't enable macros
and isn't particularly secure if they do.

Mike

> I am trying to programmaticly (vba) prevent users from editing any cell in
> one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Rick Rothstein (MVP - VB) - 28 Mar 2008 22:29 GMT

Building off of your concept, code like this should be able to be used to
return the user to the cell they were in prior to trying to go into the
forbidden column rather than just moving them over to the column next to the
forbidden column...

Dim OldCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PreviousCell As Range
Const ForbiddenColumn As Long = 1
If OldCell Is Nothing Then
Set OldCell = Cells(1, 1 - (ForbiddenColumn = 1))
End If
Set PreviousCell = OldCell
If Target.Column = ForbiddenColumn Then
MsgBox "You are not allowed in this column!"
PreviousCell.Select
End If
Set OldCell = Target
End Sub

As written, if the user attempts to go into the forbidden column right away,
he/she will be returned to A1, unless Column A is the forbidden column, in
which case he/she will be returned to B1. After that, he/she will be
returned to the previously occupied cell. We could probably fix this minor
flaw by putting OldCell in a Module, and using the Workbook_SheetActivate
event from the Workbook to store the currently active cell into the OldCell
variable. I didn't test that out; it just seems like that ought to work.

Rick

> Hi,
>
[quoted text clipped - 17 lines]
>> in
>> one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Rick Rothstein (MVP - VB) - 28 Mar 2008 22:33 GMT

And, of course, to match your example, I should have set ForbiddenColumn to
3, not the 1 I used for testing the Column A problem.

Rick

> Building off of your concept, code like this should be able to be used to
> return the user to the cell they were in prior to trying to go into the
[quoted text clipped - 50 lines]
>>> in
>>> one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Rick Rothstein (MVP - VB) - 28 Mar 2008 21:41 GMT

You might consider something like the following. Copy/Paste the code below
into the code window for the worksheet you want to block entries on. Change
the two occurrences of the number 6 (used for Column F in my example) to the
number corresponding to the column you want to block entries on. What the
code will do is let the user change the value in the column, but then it
will advise the user he can't change the existing value and then replace
that entered value with the value that was in the cell originally.

Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
On Error GoTo Whoops
Application.EnableEvents = False
MsgBox "Values in this column cannot be changed!"
Target.Value = OldValue
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then OldValue = Target.Value
End Sub

Rick

>I am trying to programmaticly (vba) prevent users from editing any cell in
> one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Rick Rothstein (MVP - VB) - 28 Mar 2008 21:51 GMT

I forgot to mention... same cautions as Mike gave you in his posting, "This
fails completely if the user doesn't enable macros and isn't particularly
secure if they do."

Rick

> You might consider something like the following. Copy/Paste the code below
> into the code window for the worksheet you want to block entries on.
[quoted text clipped - 26 lines]
>>I am trying to programmaticly (vba) prevent users from editing any cell in
>> one specific column without haveing to 'protect' the entire worksheet.

Reply to this Message

Dave Peterson - 28 Mar 2008 22:46 GMT

One more (in case the user selects multiple cells):

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("C:C")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Me.Range("a1").Select
Application.EnableEvents = True

End Sub

> I am trying to programmaticly (vba) prevent users from editing any cell in
> one specific column without haveing to 'protect' the entire worksheet.

Signature

Dave Peterson

Reply to this Message