Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Capture Worksheet Delete Event? (Excel 97)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Guy Havers
    Guest

    Re: Capture Worksheet Delete Event? (Excel 97)

    One method would be to protect the workbook (not worksheet) on open, so your user cannot delete anything.

    e.g. ActiveWorkbook.Protect Password:="test", Structure:=True (N.B. the password bit is optional)

    You could then control any housekeeping from your code, unprotecting the workbook first. Trouble is 'protection' may turn off a few other things that you want to remain active, so there is probably a better way.

    Just an idea

    Guy

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture Worksheet Delete Event? (Excel 97)

    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.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture Worksheet Delete Event? (Excel 97)

    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


    <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture Worksheet Delete Event? (Excel 97)

    Hey, now that I've made some design changes, this might work. Thanks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •