Locking a range of cells upon data entry

[SUDESH123]

Hi there, I am looking at a way on how to lock certain cells in a range after data entry in EXCEL. Before, the user can finalize their entry, there should be an option/message to prompt them to check their entry and inform them the cell will be locked. Can this be done in EXCEL VBA.

Thanks Sudesh.

 

[Logit]

What range of cells are you targeting and what is the name of sheet tab holding those cells ?

 

[Logit]

The following to be pasted into a Routine Module :

Option Explicit


Sub DataChanes()
  Dim Msg As String, Title As String
  Dim Config As Integer, Ans As Integer
  Msg = "Are You Sure ?"
  Msg = Msg & vbNewLine & vbNewLine
  Msg = Msg & "Review your entries - Cells will be locked and changes cannot be undone." & vbCrLf & vbCrLf & _
              "Click YES to finalize your entries. Click NO to make changes."
 
 
  Title = "Data Changes"
  Config = vbYesNo + vbExclamation
  Ans = MsgBox(Msg, Config, Title)
  If Ans = vbYes Then
    CellProtect
  End If
  If Ans = vbNo Then Exit Sub
End Sub

Sub CellProtect()
Dim Blatt As Worksheet, rng As Range
Set Blatt = Worksheets("Sheet1")
Set rng = Blatt.Range(Cells(1, 1), Cells(3, 7))
rng.Select
Blatt.Unprotect
Blatt.Cells.Locked = False
rng.Locked = True
Blatt.Protect
End Sub

Sub CellUnProtect()
Dim Blatt As Worksheet, rng As Range
Set Blatt = Worksheets("Sheet1")
Set rng = Blatt.Range(Cells(1, 1), Cells(3, 7))
rng.Select
Blatt.Unprotect
Blatt.Cells.Locked = False
rng.Locked = False
'Blatt.Protect
End Sub

The above code focuses on range A1:G3 for locking and protection, on Sheet 1. The code can be edited to another range
and sheet if required.

 

[NP1872]

Good morning, I have attached the file where the ranges need to be locked. These are on columns S, V, Y and AB. As soon as the user enters a "Yes" there should be a message that pops up to ask the user as to whether they want to finalise their entry. Once the entry is made the cell in that row should lock and only be allowed to open with a password. I hope this clarifies what I am looking at for assistance.

 

[vletm]

NP1872 are You same member as SUDESH123?
This thread belongs to SUDESH123.

 

[Logit]

Paste the following in the CORE sheet module :

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
    If Intersect(Target, Range("S1:S200")) Is Nothing Then Exit Sub
        If Target = "Yes" Then
            Target.Offset(0, 3).Locked = True
            Target.Offset(0, 6).Locked = True
            Target.Offset(0, 9).Locked = True
        Else
            Target.Offset(0, 3).Locked = False
            Target.Offset(0, 6).Locked = False
            Target.Offset(0, 9).Locked = False
        End If
    ActiveSheet.Protect
End Sub