Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Hi There,
I would like to trigger a maco when the nr of sheets in the active workbook changes ... Not: Private Sub Workbook_NewSheet(ByVal Sh As Object) As this triggers only when a New ("Blank") Sheet is inserted. But it should fire as well when I make a copy of an existing sheet(s). Please? Best Regards, Sige |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Here's one technique
Option Explicit Dim NumSheets Private Sub Workbook_Open() NumSheets = ThisWorkbook.Sheets.Count End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) If ThisWorkbook.Sheets.Count < NumSheets Then NumSheets = ThisWorkbook.Sheets.Count 'your code End If End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Sige" wrote in message oups.com... Hi There, I would like to trigger a maco when the nr of sheets in the active workbook changes ... Not: Private Sub Workbook_NewSheet(ByVal Sh As Object) As this triggers only when a New ("Blank") Sheet is inserted. But it should fire as well when I make a copy of an existing sheet(s). Please? Best Regards, Sige |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Hi Bob,
Thank you! 1. I am wondering though why it stumps when: after adding 1 sheet ... you delete this one. Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) If ThisWorkbook.Sheets.count < NumSheets Then NumSheets = ThisWorkbook.Sheets.count MsgBox "A sheet has been added/deleted" End If End Sub 2. I have a workbook with 2 sheets (1 veryhidden =infosheet to Enable macro's). I do add sheets upon opening. Though your "Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)" triggers already... Brgds Sige PS: Just for my curiosity: What happened to the "Purbecks"? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Oops.
1.A bit too fast ... : When deleting a sheet ...it triggers ...but only when clicking another sheet first. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Try this alternative solution, and let us know if it causes any other
problems Dim NumSheets Private Sub Workbook_Open() NumSheets = ThisWorkbook.Sheets.Count End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim sActive As Object If ThisWorkbook.Sheets.Count < NumSheets Then NumSheets = ThisWorkbook.Sheets.Count MsgBox ActiveSheet.Name End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Workbook_SheetActivate ActiveSheet End Sub The Purbecks clouded over :-)) Bob (remove nothere from email address if mailing direct) "Sige" wrote in message oups.com... Oops. 1.A bit too fast ... : When deleting a sheet ...it triggers ...but only when clicking another sheet first. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Hi Bob,
It is working fine ...though still triggering with my hidden sheet upon opening, while not any sheets added/deleted. Here is the code... Best Regards Sige '***Workbook code: Option Explicit Dim NumSheets As Integer Private Sub Workbook_Open() SheetsShow True NumSheets = ThisWorkbook.Sheets.Count End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) SheetsShow False ThisWorkbook.Saved = True ThisWorkbook.Save End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim sActive As Object If ThisWorkbook.Sheets.Count < NumSheets Then NumSheets = ThisWorkbook.Sheets.Count MsgBox ActiveSheet.Name End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Workbook_SheetActivate ActiveSheet End Sub '***Module: Sub SheetsShow(blnState As Boolean) Dim Sht As Worksheet If blnState Then For Each Sht In ThisWorkbook.Worksheets Sht.Visible = xlSheetVisible Next Sheet2.Visible = xlSheetVeryHidden Else Sheet2.Visible = xlSheetVisible For Each Sht In ThisWorkbook.Worksheets If Sht.CodeName < "Sheet2" Then Sht.Visible = xlSheetVeryHidden End If Next ActiveSheet.Activate End If End Sub Bob Phillips wrote: Try this alternative solution, and let us know if it causes any other problems Dim NumSheets Private Sub Workbook_Open() NumSheets = ThisWorkbook.Sheets.Count End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim sActive As Object If ThisWorkbook.Sheets.Count < NumSheets Then NumSheets = ThisWorkbook.Sheets.Count MsgBox ActiveSheet.Name End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Workbook_SheetActivate ActiveSheet End Sub The Purbecks clouded over :-)) Bob (remove nothere from email address if mailing direct) "Sige" wrote in message oups.com... Oops. 1.A bit too fast ... : When deleting a sheet ...it triggers ...but only when clicking another sheet first. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Hi Bob,
I am using XL97 ... Buit the switch worked! Thx a lot! PS: Just discovered your -& the late Frank Kabel!- paper "Getting The Last Value In Range"! Very Useful, Powerful, Well-documented, -explained ... Impressive! Deep bow, Still-missing-the-Purbecks-Sige |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger macro when nr of sheets changes
Thanks Sige. I still have three others we were working on when Frank's
accident happened which I really need to finish off. Regards Bob (remove nothere from email address if mailing direct) "Sige" wrote in message oups.com... Hi Bob, I am using XL97 ... Buit the switch worked! Thx a lot! PS: Just discovered your -& the late Frank Kabel!- paper "Getting The Last Value In Range"! Very Useful, Powerful, Well-documented, -explained ... Impressive! Deep bow, Still-missing-the-Purbecks-Sige |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trigger Macro by Worksheet_Change | Excel Discussion (Misc queries) | |||
macro trigger | Excel Discussion (Misc queries) | |||
Trigger a Macro in MS Access from an Excel Macro? | Excel Programming | |||
Macro trigger? | Excel Programming | |||
Macro trigger? | Excel Programming |