Capturing Sheet Events
 

I wish to have VBA "know" each time a sheet is added,
renamed or deleted. The adding is easy using the
Workbook_NewSheet sub. But what about renaming or
deleting? I could restrict the user so that sheet adds,
renames and deletes are only done under VBA control, but I
would rather let the user do things the way they want and
capture significant events behind the scenes.

Thanks

Capturing Sheet Events
 

You can capture Worksheet additions and deletions with
Workbook_SheetActivate (maintain a list of worksheets and check for
differences)

Not sure how to capture a rename event though.

Rob


"MWE" wrote in message
...
I wish to have VBA "know" each time a sheet is added,
renamed or deleted. The adding is easy using the
Workbook_NewSheet sub. But what about renaming or
deleting? I could restrict the user so that sheet adds,
renames and deletes are only done under VBA control, but I
would rather let the user do things the way they want and
capture significant events behind the scenes.

Thanks

Capturing Sheet Events
 

Not sure how you're wanting to do this but here's my suggestion.
In your workbook open event add the following.
Create a new menu item say under tools called Check Workbook. Like so:
Const MenuItemName = "Import Plan Budgets" 'replace with the name of your
menu name
Const MenuItemMacro = "ImportPlan" 'replace with the name of your macro

'delete menu item in case already there
On Error Resume Next


Application.CommandBars(1).Controls("Tools").Contr ols(MenuItemName).Delete
'in case of error tells you what is happening
On Error GoTo DidntHappen
'create the new menu item
Set newitem = Application.CommandBars(1).Controls("Tools").Contr ols.Add
'specify the menu caption and macro
newitem.Caption = MenuItemName
newitem.OnAction = MenuItemMacro
'add a separator bar
newitem.BeginGroup = True
Exit Sub
'error handler
DidntHappen:
MsgBox "An error occured, do not panic"

Create you a userform. On one side add 3 labels, set the caption of label 1
to todays date, label 2 to say something like on the date above this
workbook contained the following sheets, label 3 list the sheet names.

Beside label one add a textbox, beside label 2 add another label and set its
caption to something like "On the date above this workbook now contains the
following sheets. Beside label 3 add a combobox.

Right click the userform then go to the initialize event and add:
Textbox1.Value = Date
Dim w as worksheet
For Each w in Activeworkbook.Worksheets
Combobox1.Additem w.Name
Next w

Now create your MenuItemMacro and just have it say either Load Userform1or
Userform1.Show

Now anytime you want to check what was deleted, added, renamed, ect.. just
click Tools, the menu item name you added and the userform will pop up and
display what the workbook started with and what the workbook now contains.

Looked pretty neat when I just did it.
Good luck

"MWE" wrote in message
...
I wish to have VBA "know" each time a sheet is added,
renamed or deleted. The adding is easy using the
Workbook_NewSheet sub. But what about renaming or
deleting? I could restrict the user so that sheet adds,
renames and deletes are only done under VBA control, but I
would rather let the user do things the way they want and
capture significant events behind the scenes.

Thanks

Capturing Sheet Events
 

Cliff: Thanks for your reply. There are lots of ways to
do this IF I write some/lots of VBA and force the user to
use the VBA supported method. In other applications where
real confusion will occur with new sheets added ad-hoc, I
added a few menu picks to add sheets, delete sheets, etc.
But in this application, I want the user to be able to add
sheets and rename or delete sheets (except for a few key
ones) using Excel's standard functionality. I want to
trap the events behind the scenes and keep track of what
the user has done.


-----Original Message-----
Not sure how you're wanting to do this but here's my
suggestion.
In your workbook open event add the following.
Create a new menu item say under tools called Check
Workbook. Like so:
Const MenuItemName = "Import Plan Budgets" 'replace with
the name of your
menu name
Const MenuItemMacro = "ImportPlan" 'replace with the name
of your macro

'delete menu item in case already there
On Error Resume Next


Application.CommandBars(1).Controls("Tools").Cont rols
(MenuItemName).Delete
'in case of error tells you what is happening
On Error GoTo DidntHappen
'create the new menu item
Set newitem = Application.CommandBars(1).Controls
("Tools").Controls.Add
'specify the menu caption and macro
newitem.Caption = MenuItemName
newitem.OnAction = MenuItemMacro
'add a separator bar
newitem.BeginGroup = True
Exit Sub
'error handler
DidntHappen:
MsgBox "An error occured, do not panic"

Create you a userform. On one side add 3 labels, set the
caption of label 1
to todays date, label 2 to say something like on the date
above this
workbook contained the following sheets, label 3 list the
sheet names.

Beside label one add a textbox, beside label 2 add
another label and set its
caption to something like "On the date above this
workbook now contains the
following sheets. Beside label 3 add a combobox.

Right click the userform then go to the initialize event
and add:
Textbox1.Value = Date
Dim w as worksheet
For Each w in Activeworkbook.Worksheets
Combobox1.Additem w.Name
Next w

Now create your MenuItemMacro and just have it say either
Load Userform1or
Userform1.Show

Now anytime you want to check what was deleted, added,
renamed, ect.. just
click Tools, the menu item name you added and the
userform will pop up and
display what the workbook started with and what the
workbook now contains.

Looked pretty neat when I just did it.
Good luck

"MWE" wrote in
message
...
I wish to have VBA "know" each time a sheet is added,
renamed or deleted. The adding is easy using the
Workbook_NewSheet sub. But what about renaming or
deleting? I could restrict the user so that sheet adds,
renames and deletes are only done under VBA control,
but I
would rather let the user do things the way they want
and
capture significant events behind the scenes.

Thanks


.

Capturing Sheet Events
 

Rob: thanks for the idea. I already maintain a list of
sheets so comparing current sheets with the list each time
any sheet is activated should work. That might work for
the rename problem too, I will wrok on that ...
-----Original Message-----
You can capture Worksheet additions and deletions with
Workbook_SheetActivate (maintain a list of worksheets and
check for
differences)

Not sure how to capture a rename event though.

Rob


"MWE" wrote in
message
...
I wish to have VBA "know" each time a sheet is added,
renamed or deleted. The adding is easy using the
Workbook_NewSheet sub. But what about renaming or
deleting? I could restrict the user so that sheet adds,
renames and deletes are only done under VBA control,
but I
would rather let the user do things the way they want
and
capture significant events behind the scenes.

Thanks


.