Geek asks and answers

Excel VBA prevents cell deletion but allows editing

I made a table that the user can enter a zip code and a quantity of 2 cells, and I have other cells that perform calculations and display the results.

I added several VBAs so that no one could delete rows and columns, but I would like to prevent the deletion of any cell within the range, but also allow the user to make changes to certain cells, and also prevent editing cells with the formula there.

In the cell, the E4user can enter a zip code. The E6user can enter the quantity. They can be edited, but not deleted. E8:E9and E11:E14- all drop-down lists (validation) containing data from the lists. They can be changed using the drop-down list, but not deleted.

L10:L14, L16, L23:L27, L29, L30:L33They can edit their data, but not removed.

What will be the VBA for this? I guess he would use Worksheet_Change() event.

+3
source share
2 answers

Is that what you are trying? Users can edit cells E4and E6, but they cannot leave it blank. I also assume that the cell is not empty in front of the hand.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("E4")) Is Nothing Then
        If Len(Trim(Range("E4").Value)) = 0 Then Application.Undo
    ElseIf Not Intersect(Target, Range("E6")) Is Nothing Then
        If Len(Trim(Range("E6").Value)) = 0 Then Application.Undo
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Followup

, . ? IF THEN CASE ? - AdRock 2

/ , .

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("E4,E6,E8:E9,E11:E14,L10:L14,L16,L23:L27,L29,L30:L33")) Is Nothing Then
        If Len(Trim(Target.Value)) = 0 Then Application.Undo
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub
+5

, Workheet_Change() , . , , , Worksheet_Change(), () .

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$4" Then
        ' check the previous value on the hidden sheet here, if changed, then save it, if empty, then restore it
    End If
End Sub
+1

More articles:


All Articles