• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locking a range of cells upon data entry

SUDESH123

New Member
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.
 
The following to be pasted into a Routine Module :

Code:
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.
 

Attachments

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.
 

Attachments

Paste the following in the CORE sheet module :

Code:
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
 

Attachments

Back
Top