CODEWITHSUNDEEP
vbaexcelcells
kendj
kendj

Reputation: 51

How to make a specific cell in excel uneditable

My problem is I'm trying to make a cell cannot be edited by the users after the inputs are given e.g. I want to let the user input something from cell C22-C28 then after the user inputs the cell, that specific cell shouldn't be edited. What should be the best solution for this? Any suggestions?

Upvotes: 0

Views: 5338

Answers (2)

user5857081
user5857081

Reputation:

Maybe you can try the code below:

Option Explicit
Public OriginalCell As Range
Public ProtectedCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")

lastRow = Cells(Rows.Count, 3).End(xlUp).Row + 1

If Not Intersect(Target, Range("C1:D" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)) Is Nothing Then
Application.ScreenUpdating = False
Set ProtectedCell = Target
MsgBox "Access Denied", vbExclamation, "Access Status"
Cells(lastRow, 3).Select
Application.ScreenUpdating = True

End If

Hope it will help solve your problem.

Upvotes: 0

Luboš Suk
Luboš Suk

Reputation: 1546

Take a look at this question How to Lock the data in a cell in excel using vba

where are multiple nice answers. In basic, you need to set your range to .locked and then protect sheet via .protect, for more develop info try to look at msdn

Edit: so you can use this as mentioned in this question (Restricting the user to delete the cell contents)

Without lock and unlock, you can use this. We have there one global variable to store selection value (to preserve beforechange state). Function SelectionChange, updating value of current cell, so we can restore cell value after users try.

Sub worksheet_change just controling, if user targeting specified row and column (can be adjusted for whole range), and if he try to change value, he is prompted and value is set back.

Dim prevValue As Variant

Private Sub worksheet_SelectionChange(ByVal target As Range)

    prevValue = target.Value

End Sub

Private Sub worksheet_change(ByVal target As Range)



    If target.Row = 5 And target.Column = 5 Then

        If target.Value <> prevValue Then
            target.Value = prevValue


            MsgBox "You are not allowed to edit!", vbCritical + vbOKOnly

        End If

    End If


End Sub

Upvotes: 0

Related Questions