Excel does an event get triggered when you delete a sheet? :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

does an event get triggered when you delete a sheet?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

pliant
Board Master


Joined: 09 Jan 2003
Posts: 202

Flag: Canada

Status: Offline

 Reply with quote  

is there any event triggered when you delete a sheet?

when a user deletes a sheet i would also like to delete that sheet from a list of sheets that i have on another sheet...

Post Thu Jan 16, 2003 9:08 pm 
 View user's profile Send private message

TommyGun
MrExcel MVP


Joined: 10 Dec 2002
Posts: 3282
Location: Clear Lake, TX
Flag: Usa

Status: Offline

 Reply with quote  

I do not know of an event that is triggered when a sheet is deleted.

There may be another way to accomplish what you are looking for though. Can you explain why you need to delete the sheet name from a list of names?

Post Thu Jan 16, 2003 9:15 pm 
 View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger

bobm
Board Master


Joined: 12 Jan 2003
Posts: 359


Status: Offline

 Reply with quote  


How about code in the Sheet_Activate event of the sheet with the list that will iterate through the sheets in the workbook and generate a fresh list?

Post Thu Jan 16, 2003 9:36 pm 
 View user's profile Send private message

pliant
Board Master


Joined: 09 Jan 2003
Posts: 202

Flag: Canada

Status: Offline

 Reply with quote  

i have a list of items that populates a combo box and a separate sheet for each item.

if the user deletes a sheet i would like that item removed from the combo box as well so the user cannot select it.

Post Fri Jan 17, 2003 4:20 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8201

Flag: Uk

Status: Offline

 Reply with quote  

Put this code in the ThisWorkbook module in a blank workbook with 2 sheets:

code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox Sh.Name & " activated"
End Sub




Then delete a sheet. The event is triggered.

You can replace the MsgBox statement with the code to update your list of sheets.

Post Fri Jan 17, 2003 4:45 pm 
 View user's profile Send private message

pliant
Board Master


Joined: 09 Jan 2003
Posts: 202

Flag: Canada

Status: Offline

 Reply with quote  

ok thanks...but how do you iterate throught the sheets in the workbook??

Post Fri Jan 17, 2003 6:01 pm 
 View user's profile Send private message

pliant
Board Master


Joined: 09 Jan 2003
Posts: 202

Flag: Canada

Status: Offline

 Reply with quote  

I think I've figured it out. This seems to work:

'get the name of the sheet being deactivated
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
sheetName = Sh.Name
End Sub

'check if it is still there when the next sheet is activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sheetCount As Integer
Dim found As Boolean

sheetCount = Sheets.Count

For i = 1 To sheetCount
If Sheets(i).Name = sheetName Then
MsgBox ("sheet found")
found = True
End If
Next

If Not found Then
MsgBox ("sheet NOT found!")
'delete from list
End If

End Sub

[ This Message was edited by: pliant on 2003-01-17 13:17 ]

Post Fri Jan 17, 2003 6:14 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8201

Flag: Uk

Status: Offline

 Reply with quote  

This worked for me:

code:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim x As Integer
    Dim WS As Worksheet
'   *** Sheet containing list of worksheets - change to suit ***
    Set Sh = Worksheets("Sheet1")
'   *** Range containing worksheet names - change to suit ***
    Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlDown).Row)
    For x = Rng.Rows.Count To 1 Step -1
        On Error Resume Next
        Set WS = Worksheets(Rng.Cells(x, 1).Text)
        If Err <> 0 Then
            Rng.Cells(x, 1).EntireRow.Delete
        End If
    Next x
End Sub




It assumes that your list of worksheets starts in A2 on Sheet1 - change to suit.

Post Fri Jan 17, 2003 6:23 pm 
 View user's profile Send private message

pliant
Board Master


Joined: 09 Jan 2003
Posts: 202

Flag: Canada

Status: Offline

 Reply with quote  

how do you get your code to appear between the lines like that?

Post Fri Jan 17, 2003 6:42 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8201

Flag: Uk

Status: Offline

 Reply with quote  

Put

code:
immediately before your code and

immediately after it. BBCode must be enabled.

Post Sat Jan 18, 2003 7:49 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.