Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet delete event
Excel 2007, Win 7
I have a bunch of sheets, each named a person's name. I have a list of these names that I use in a DV cell. When the user deletes one of these sheets, I would like for the list to be changed to reflect that sheet deletion. How can I capture the event of a sheet deletion? Thanks for your time. Otto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet delete event
I was thinking that you should be able to use the SheetDeactivate event for
the workbook with a public variable to track the sheet count, but I couldn't get it to work in the first couple of tries in xl03. First it did not recognize the deletion by code as a deactivate event, then when I added Select to force it to recognize the change, it gave me an erroneous count on the sheets. So, I gave up on that approach. "Otto Moehrbach" wrote in message ... Excel 2007, Win 7 I have a bunch of sheets, each named a person's name. I have a list of these names that I use in a DV cell. When the user deletes one of these sheets, I would like for the list to be changed to reflect that sheet deletion. How can I capture the event of a sheet deletion? Thanks for your time. Otto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet delete event
Thanks for you help. I setup the Sheet_Deactivate in the Workbook module
(not the sheet module) and it fired when I deleted a sheet and it returned the removed sheet name in the variable Sh. Thanks again for that idea. Otto "JLGWhiz" wrote in message ... I was thinking that you should be able to use the SheetDeactivate event for the workbook with a public variable to track the sheet count, but I couldn't get it to work in the first couple of tries in xl03. First it did not recognize the deletion by code as a deactivate event, then when I added Select to force it to recognize the change, it gave me an erroneous count on the sheets. So, I gave up on that approach. "Otto Moehrbach" wrote in message ... Excel 2007, Win 7 I have a bunch of sheets, each named a person's name. I have a list of these names that I use in a DV cell. When the user deletes one of these sheets, I would like for the list to be changed to reflect that sheet deletion. How can I capture the event of a sheet deletion? Thanks for your time. Otto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet delete event
One of the thing that happens when the active sheet is deleted is that
another sheet is activated. We need to keep track of the number of sheets and when this value drops, alert that a sheet has been deleted. In the woriksheet code area of each worksheet, insert: Private Sub Worksheet_Activate() If IsEmpty(wCount) Then wCount = Worksheets.Count Exit Sub Else If wCount Sheets.Count Then MsgBox "A sheet has been deleted" Exit Sub End If End If If wCount < Sheets.Count Then MsgBox "A sheet has been added" wCount = Sheets.Count End If End Sub and in a standard module, insert the single line: Public wCount As Integer This solution may not be 100% solid, but you get the general idea. -- Gary''s Student - gsnu201001 "Otto Moehrbach" wrote: Excel 2007, Win 7 I have a bunch of sheets, each named a person's name. I have a list of these names that I use in a DV cell. When the user deletes one of these sheets, I would like for the list to be changed to reflect that sheet deletion. How can I capture the event of a sheet deletion? Thanks for your time. Otto . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet delete event
I must have been in sleep mode when I tried this the first time. If the
public variable is initialized in the public module it works fine. But if it is initialized in the private module, the value is zero in the public module and a bad result occurs when using the variable after the event code runs. I don't know if this is by design or an anomaly, but keep it in mind as you develop your code. "Otto Moehrbach" wrote in message ... Thanks for you help. I setup the Sheet_Deactivate in the Workbook module (not the sheet module) and it fired when I deleted a sheet and it returned the removed sheet name in the variable Sh. Thanks again for that idea. Otto "JLGWhiz" wrote in message ... I was thinking that you should be able to use the SheetDeactivate event for the workbook with a public variable to track the sheet count, but I couldn't get it to work in the first couple of tries in xl03. First it did not recognize the deletion by code as a deactivate event, then when I added Select to force it to recognize the change, it gave me an erroneous count on the sheets. So, I gave up on that approach. "Otto Moehrbach" wrote in message ... Excel 2007, Win 7 I have a bunch of sheets, each named a person's name. I have a list of these names that I use in a DV cell. When the user deletes one of these sheets, I would like for the list to be changed to reflect that sheet deletion. How can I capture the event of a sheet deletion? Thanks for your time. Otto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Chart Event | Charts and Charting in Excel | |||
Copy Sheet causes Combo Box change event to fire on original sheet | Excel Programming | |||
Can you trap the Delete event? | Excel Programming | |||
Delete Sheet event? | Excel Programming | |||
How to trap delete row event and hide column event? | Excel Programming |