Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing excel insert/delete events. | Excel Worksheet Functions | |||
Capturing Insert/Delete Events in Excel Sheet using c# | Excel Worksheet Functions | |||
Capturing Top 5 Performers | Excel Worksheet Functions | |||
Capturing Date | New Users to Excel | |||
Capturing Checkbox Events | Excel Programming |