CODEWITHSUNDEEP
excelvba
BruceWayne
BruceWayne

Reputation: 23283

Prevent user from editing cell, except for actual cell data

I have a spreadsheet that I'm using to keep track of when reports are sent to me. For those reports I don't receive, I have a black cell (a cell filled black). Locking cells doesn't really work, since they can't change the data in the cells. (Full disclosure, it's a conditional format doing the filling, if the cell has "Client" in the cell, it fills black).

How can I prevent users from changing the "client" cells? Some of the cells aren't black, others are. I know there's a Worksheet_Change that I could use, but I can't quite get that to determine the cell color before the user changes it.

I was thinking I'd need perhaps a Worksheet_SelectionChange event?

The crux is I can't think of a way to check a cell's fill color before a change, and if it's different after the change, do a messagebox or whatever. (Actually, I'd be checking if the cell was "Client" before the user changed it, and if so, don't allow a change).

Am I overthinking this and missing something obvious? I'd like to find a solution where I don't just program which cells are filled, and check if those cells were changed.

Edit: I just thought to try and combining those two Change events, and this seems to work, but I am not sure if it's the best way to do this: (This is in the worksheet module)

Option Explicit
Dim isClient As Boolean

Private Sub worksheet_SelectionChange(ByVal target As Range)
If target.Column = 5 Or target.Column = 6 Then
    If target.Value = "Client" Then
        isClient = True
    Else
        isClient = False
    End If
End If
End Sub


Private Sub Worksheet_Change(ByVal target As Range)
If target.Column = 5 Or target.Column = 6 And isClient = True Then
    Debug.Print "This has 'client'"
    target.Value = "Client"
    isClient = False
End If
End Sub

Upvotes: 1

Views: 624

Answers (1)

Fadi
Fadi

Reputation: 3322

To know the old color before changing we can use Application.Undo 

Private Sub Worksheet_Change(ByVal target As Range)
  Dim newValue As Variant
  If target.Column = 5 Or target.Column = 6 Then

   newValue = target.Value
   Application.EnableEvents = False
    Application.Undo
    Debug.Print target.Value 'old value
    Debug.Print target.DisplayFormat.Interior.Color 'old color
    'if it_is_OK then target.Value = newValue
   Application.EnableEvents = True

  End If
End Sub

Or simply you can lock the cell when its value changed to "Client" :

Private Sub Worksheet_Change(ByVal target As Range)

  If target.Column = 5 Or target.Column = 6 And target.Value = "Client" Then

    target.Worksheet.Unprotect
     target.Locked = True
    target.Worksheet.Protect

  End If
End Sub

Upvotes: 1

Related Questions