Capture Worksheet Delete Event? (Excel 97)

Topic

New Reply

Hi!

I’m usually an Access programmer, so maybe I’m trying to do something I can’t….Anyway, I have a program I built in Excel. Basically, each sheet ends up being one year, with cells linked between the sheets so that the totals flow from one to the other. As the user needs a new year, they click a button and hte next year is created. All well and good.

Now here’s where it falls apart: what if the user deletes a sheet, for example the first sheet where everything starts? Then all the carryforwards get messed up. So what I’d like to do is to trap the event when the user goes to delete a worksheet, then before it’s actually deleted I can convert the cells in the next year (the following worksheet) into values, so the following worksheets won’t all end up with errors. Does that make sense?

In essence, what I’m looking for is a “Before Delete” event, or at least something to trap the user’s right click on the worksheet tab. (The Worksheet Right Click event didn’t work for the tab.)

Any ideas?

Thanks!

Cecilia

Reply | Quote

Replies

Well, that’s not really what I wanted to do. I just wanted to be able to capture the event so I could do my own error handling and cleanup, because I want them to be able to delete sheets.

I did come up with a solution for my application’s problem, although I’m still interested in why we don’t have access to this event. I haven’t played with XP yet, does anyone know if that’s any different?

In case anyone’s interested, here’s what I did: my worksheets are no longer linked by formulas. Instead, I wrote code that verified whether the source worksheet (the one that was formerly linked) exists, and if it does and one of its numbers change, then it updates the total on the target worksheet. All the formulas are in code and there are no formulas in the worksheet. So if one of the worksheets is missing, all that happens is that the target worksheet’s totals become static. This is pretty much what I wanted in the first place.

Reply | Quote

Since you don’t want to protect the workbook (the only way to prevent the sheet from being deleted) you can use the workbook_sheetchanged() event which gets triggered any time a sheet in a workbook gets deactivated (a global equivalent to the worksheet_deactivate() event).You could then test if the sheet name is the one which you do not want deleted and then take action from there. There is no event to warn you before the sheet has been deleted (I wish there was, I’d use it too) so I’m curious what you’ll do programmatically to fix the deleted sheet.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Debug.Print “sheet deactivate: ” & Sh.Name
If sh.name = “donot_delete_sheet” then
‘ do whatever….
end if
End Sub

I don’t know if XP has added more events.

Deb

Reply | Quote

Hey, now that I’ve made some design changes, this might work. Thanks!

Reply | Quote