View Full Version : Getting the value that WAS in the cell just deleted
chamster
08-29-2007, 01:55 PM
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
08-29-2007, 02:00 PM
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
08-29-2007, 02:07 PM
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
08-29-2007, 09:30 PM
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
08-29-2007, 11:46 PM
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
08-29-2007, 11:51 PM
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
08-30-2007, 04:16 AM
RIght. Got it. Thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.