Getting the value that WAS in the cell just deleted

[chamster]

I have a solution where a new sheet is created as a user puts in an id into a cell. That sheet is then populated from a database. As the user enters next id, i create a new sheet and populate it as well. The name of a sheet is that id.

What i'd like to do is to remove the sheet with id 1234 if the user deletes that number. How do i obtain the information just deleted?

[Bob Phillips]

Private prev

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(prev).Delete
Application.DisplayAlerts = True
On Error GoTo 0
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
prev = Target.Value
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mdmackillop]

Hi Chamster
Welcome to VBAX
You can record the value when a cell is entered, then test by Find if the value still exists after the sheet is changed. These are Sheet Events and need to be placed in the Worksheet module.
Regards
MD

Option Explicit

Dim LastVal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then LastVal = Target.Text
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Columns(1).Find(LastVal, lookat:=xlWhole)
On Error Resume Next
Application.DisplayAlerts = False
If c Is Nothing Then Sheets(LastVal).Delete
Application.DisplayAlerts = True
End Sub

[chamster]

Perfect! This is the thing i was looking for. The elegance/effectivity of the code is not an issue so i won't post any samples. Thanks for the help.

[chamster]

This was strange... I'm looking at the code on the forum and it looks right. However, the copy of the reply sent to my mail box contains few errors...

[mdmackillop]

Somebody passing by possibly noticed the misplaced End If and End With and just shuffled them about!
BTW, Inserting a new cell in H1 to receive a new number will delete the sheet, so a little caution with this approach.

[chamster]

RIght. Got it. Thanks!