Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Disable to delete cells but enable to insert rows

0

I have this VBA code to disable to delete some cells, but this blocked me to insert a new row, when i try to insert a new row I got the MsgBox "You can't clear this cell", so what I can do to have both , disable to delete and enable to insert?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Application.Intersect(Target, Range("E1:E2000, G1:I2000, K1:Q2000, S1:S2000")) Is Nothing Then Exit Sub
    For Each Cell In Target.Cells
        If Len(Cell.Value) = 0 Then
            Application.EnableEvents = False
            Application.Undo
            MsgBox "You can't clear this cell", vbExclamation, "Recipe Calculator"
            Application.EnableEvents = True
            Exit For
        End If
    Next Cell
End Sub
Answer
Discuss

Discussion

Sorry, can't download your file. Please post your code in your question.

@Don It seems that I always have this problem with non-Excel files, but could be the time of day.
Variatus (rep: 4889) Aug 22, '17 at 9:26 pm
Hi Variatus I post my code in my question. 
JAExcel (rep: 10) Aug 23, '17 at 10:15 am
Hm, it seems to work fine for me to download. :/
don (rep: 1989) Aug 23, '17 at 1:01 pm
@Don You are nearer to the trough, lol:
Variatus (rep: 4889) Aug 23, '17 at 9:50 pm
Add to Discussion

Answers

0

The problem with your macro is that it simply undoes anything that you are trying to do with the cells/ranges in question.

Given your macro,  you will not, I believe, be able to achieve this. There are ways around this but they can quickly become complicated if you want to start allowing only certain events here and there.

I think that you should look into protecting the worksheet and then, when you go to do that (Review tab > Protect Sheet) you can choose to allow the user to insert rows and columns. And, if you only want some cells to be able to have anything done to them, just select them and right-click > format cells > Protection tab > uncheck the Locked option - do this before you go to protect the sheet.

Discuss

Discussion

I dont want to lock the cell, I want that the user should be able to change the value in the cell, but not to delete it.
so what i do, I make a data validation on the cell to allow numbers between 1 - 500 and I uncheck the "Ignore blank" checkbox, so the user can't clear the cell with the backspace, but the delete button is still available,

also I have on the sheet Data group to hide and show rows with the + and -, and it's dont work when the sheet is protected
that's the reason why i use the vba to disable the delete,
JAExcel (rep: 10) Aug 24, '17 at 11:13 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login