locking specific cells in columns

[mark.wagner]

Thanks in advance for all you do.

I have some cells in each column available for data entry; other cells are formulas that I have protected in the usual manner. What I want to accomplish is a button (or some other mechanism) where I can lock the data entry cells just for that column. The idea is to protect prior month's data from accidental changes. On second thought, locking ALL the cells for that column would work just fine, too, since all cells are either data or formulas. duh.

Upon locking, I'd like for the button to change to "unlock mode" so that if pressed again it will unlock the same cells, but leaving the formula cells still protected. Using two overlapping button controls and alternately hiding and making visible doesn't bother me (although that's probably shoddy programming to you guys).

I'm not worried about security as much as just preventing inadvertent overwrites, etc and still being able to easily unlock the cells if I need to edit.

thanks again,

Mark Wagner, CPA

 

[tactps]

Do you have a specific row that contains data entry that is consistent?

 

[tactps]

Assuming that your data allows entry into row 2:

Private Sub CommandButton1_Click()
ReturnCell = ActiveCell.Address
ActiveSheet.Unprotect
ActiveCell.Offset(-ActiveCell.Row + 2, 0).Activate
If ActiveCell.Locked = False Then
ActiveCell.EntireColumn.Locked = True
Else
ActiveCell.EntireColumn.Locked = False 'You may need to change this to pick up the data entry cells
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range(ReturnCell).Activate
End Sub

 

[mark.wagner]

hmmmmm

not exactly what I wanted, but I think you've pointed me in the direction I needed.

I think I can use this to unlock the column, change the locking for the data cells, and then relock the column to protect the formulas.

cool. thanks much.

 

[mark.wagner]

what I came up with

just for anybody who might find it useful

Private Sub CommandButton1_Click()

Dim rowindex As Integer
If CommandButton1.Caption = "Unlock Column" Then

CommandButton1.Caption = "Lock Column"
CommandButton1.BackColor = RGB(255, 0, 0)
CommandButton1.ForeColor = RGB(255, 255, 255)
ActiveSheet.Unprotect

For rowindex = 5 To 58

With Worksheets("West").Cells(rowindex, 4)

If .HasFormula = False Then
.Locked = False
Else: .Interior.Pattern = xlGray8
End If
End With

Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Else

CommandButton1.Caption = "Unlock Column"
CommandButton1.BackColor = RGB(200, 200, 200)
CommandButton1.ForeColor = RGB(0, 0, 0)
ActiveSheet.Unprotect

For rowindex = 5 To 58

With Worksheets("West").Cells(rowindex, 4)

.Locked = True
.Interior.Pattern = xlSolid
End With

Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End If

End Sub