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

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

"DRICE" wrote:

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

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


"DRICE" <(E-Mail Removed)> wrote in message
news:2B5FC1F5-4CC0-4B73-A7C3-(E-Mail Removed)...
>I am trying to programmaticly (vba) prevent users from editing any cell in
> one specific column without haveing to 'protect' the entire worksheet.

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


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:O$(E-Mail Removed)...
> 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
>
>
> "DRICE" <(E-Mail Removed)> wrote in message
> news:2B5FC1F5-4CC0-4B73-A7C3-(E-Mail Removed)...
>>I am trying to programmaticly (vba) prevent users from editing any cell in
>> one specific column without haveing to 'protect' the entire worksheet.
>

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


"Mike H" <(E-Mail Removed)> wrote in message
news:54E00400-B497-4FB3-BB57-(E-Mail Removed)...
> 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
>
> "DRICE" wrote:
>
>> I am trying to programmaticly (vba) prevent users from editing any cell
>> in
>> one specific column without haveing to 'protect' the entire worksheet.

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


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
> "Mike H" <(E-Mail Removed)> wrote in message
> news:54E00400-B497-4FB3-BB57-(E-Mail Removed)...
>> 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
>>
>> "DRICE" wrote:
>>
>>> I am trying to programmaticly (vba) prevent users from editing any cell
>>> in
>>> one specific column without haveing to 'protect' the entire worksheet.
>

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



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

--

Dave Peterson