Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
I am trying to programmaticly (vba) prevent users from editing any cell in
one specific column without haveing to 'protect' the entire worksheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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" wrote in message ... I am trying to programmaticly (vba) prevent users from editing any cell in one specific column without haveing to 'protect' the entire worksheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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)" wrote in message ... 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" wrote in message ... I am trying to programmaticly (vba) prevent users from editing any cell in one specific column without haveing to 'protect' the entire worksheet. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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)" wrote in message ... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I disable cell editing in vba?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable users from editing an excel sheet once they open | Excel Discussion (Misc queries) | |||
Editing in a cell | New Users to Excel | |||
Disable Editing tool bars | Excel Discussion (Misc queries) | |||
Disable CommandBar buttons while editing the cell | Excel Programming | |||
disable cells editing | Excel Discussion (Misc queries) |