CODEWITHSUNDEEP
vbaexcel
user5857081
user5857081

Reputation:

Disabling edit property in Excel

Anyone knows how to disable the edit property in excel cells?

What I want is, if the user directly click (not double click) the cell, it will prompt a message that says:

You are not allowed to edit the content!

I have this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
    If Not Intersect(Target, Range("C21:D" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)) Is Nothing Then
        Cancel = True
        MsgBox "You are not allowed to edit the content!", vbCritical + vbOKOnly
    End If
End Sub

But it returns an error saying :

enter image description here

Upvotes: 2

Views: 134

Answers (4)

user5857081
user5857081

Reputation:

I have finally done it this way:

Option Explicit

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

Thanks all for the help and ideas!

Upvotes: 2

0m3r
0m3r

Reputation: 12499

How about if you do ...

Option Explicit
Public OriginalCell As Range
Public ProtectedCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Set OriginalCell = [A5]

    If Not Intersect(Target, [A1:B1]) Is Nothing Then
        Application.ScreenUpdating = False
        Set ProtectedCell = Target
        MsgBox "Access Denied", vbExclamation, "Access Status"
        OriginalCell.Select
        Application.ScreenUpdating = True
    Else
        Set OriginalCell = Target
    End If
End Sub

Click, Double click or Select sheet Prompt MsgBox

Edit

Option Explicit
Public OriginalCell As Range
Public ProtectedCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set OriginalCell = Range("C22")

    If Not Intersect(Target, Range("C21: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"
        OriginalCell.Select
        Application.ScreenUpdating = True
    Else
        Set OriginalCell = Target
    End If
End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

You don't need VBA for this, that's a functionality built into Excel already.

Simply protect the sheet (with or without a password); all "locked" cells will no longer be editable. Change which cells are locked using cell properties (Ctrl+1), in the "Protection" tab.

You can programmatically toggle worksheet protection using the Worksheet.Protect / Worksheet.Unprotect methods.

Upvotes: 1

Varon
Varon

Reputation: 3916

You need to define the sub for SelectionChange

Try SelectionChange instead of BeforeDoubleClick in your snippet

Upvotes: 0

Related Questions