VBA code needed to repopulate a cell if it's deleted and show a message box.

[Jed Shields]

Hi guys,

I've got some code that populates a table with data from another table whenever a date is selected from a dropdown list (in cell B9).

I'm trying to add some more functionality so that if someone deletes a cell in the first table, then it will repopulate it and throw a message box saying "Oi!, Stop it!" ... or something a bit more diplomatic I can't simply protect and lock the cells as I need to be able to click on a cell to get some other information and RAG status using active cell formula. The second table, which contains all the important formula is protected though.

The sample code shows it working for a single cell, but I need to extend this to a range and it keeps falling over. Any ideas greatly appreciated.

Cheers,

Jed.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("$b$9") Then
    
Application.EnableEvents = False
    
   Call PopulateSummaryTable
    
Application.EnableEvents = True
    
End If


'If Range("e6") = "" Then

 '       Sheet27.Range("e6").Value = Sheet27.Range("e64").Value
  '      MsgBox "Please don't delete cell values."
'End If


End Sub

PolulateSummaryTable:

Sub PopulateSummaryTable()

Sheet27.Range("e6:Q20").Value = Sheet27.Range("e64:Q78").Value

End Sub

 

[Pho]

Would it not be easier lock the cell and protect the sheet rather than having to detect deletions?

 

[Jed Shields]

Yes. However, check my second paragraph:

"I can't simply protect and lock the cells as I need to be able to click on a cell to get some other information and RAG status using active cell formula. The second table, which contains all the important formula is protected though."

 

[wesimmo]

If Not Application.Intersect(Target, Range("a1:a10")) Is Nothing Then

Change the range as appropriate.

edit:

Though you could lock the cells, you can still select them and copy data out of them if you select the right tick boxes on the Protect Sheet function.

Any issues with having to change the values you could deal with in the VBA, i.e. unlock the cells, change the data, lock the cells.

I don't believe that conditional formatting will have any issues with the cells being protected.

 

[Jed Shields]

I managed to get it working with this, using a similar Not Intersect function:

Sub DataDelete()

If Not Intersect(Range("A:CV"), Target) Is Nothing Then


Private Sub Worksheet_Change(ByVal Target As Range)


If Target = Range("$b$9") Then
    
Application.EnableEvents = False
    
   Call PopulateSummaryTable
    
Application.EnableEvents = True
    
End If


If Not Intersect(Range("E6:Q20"), Target) Is Nothing Then
    If Target.Value = "" Then
        Target.Value = Target.Offset(58, 0).Value
        MsgBox "Please don't delete cell values. Change data through Data Entry only", vbExclamation
    End If
End If


End Sub

Thanks for the help guys

 

[Pho]

Yes. However, check my second paragraph:

"I can't simply protect and lock the cells as I need to be able to click on a cell to get some other information and RAG status using active cell formula. The second table, which contains all the important formula is protected though."

Oops. Skimming threads is destined for failure .