Prevent worksheet deletion
Posted by Paul on April 17, 2001 7:44 PM
Can anyone tell me how to call a subroutine when a user attempts to delete a worksheet? Also,how can I programatically write code into the code module of a new worksheet? (Not a new project module.) I have written a macro to insert a new worksheet into an open workbook, and then fill the desired cells in that sheet with formulas. With the Workbook_BeforeClose event a duplicate check is run on the primary sheet, then the new sheet is deleted and the workbook is saved. I don't want the user to delete the worksheet before the Workbook_BeforeClose event. Any help will be much appreciated! Paul
Posted by Dave Hawley on April 18, 2001 2:45 AM
Hi Paul
Here is some code that creates a new sheet and adds some code to the Sheet_Activate Event.
Sub AddSheetWithEventCode()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'You must add a reference to _
Microsoft Visual Basic for Applications Extensibility
'Tools>Reference
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim VBCodeModule As VBIDE.CodeModule
Dim Wsht As Worksheet
Dim LNum As Long
'Add a new worksheet to the active workbook _
and set it to a Worsheet variable.
Set Wsht = ActiveWorkbook.Worksheets.Add
'Set a reference to the event module of the new sheet
Set VBCodeModule = _
Wsht.Parent.VBProject.VBComponents(Wsht.Name).CodeModule
'Creat the new sheet event procedure(Sheet_Activate)
LNum = VBCodeModule.CreateEventProc("Activate", "Worksheet")
'Write some code into the module
VBCodeModule.InsertLines LNum + 1, "MsgBox ""I'm a new sheet!"""
VBCodeModule.InsertLines LNum + 2, "MsgBox ""What do you think?!"""
'Release memory
Set VBCodeModule = Nothing
Set Wsht = Nothing
End Sub
Another way to do this is to make a Workbook with only one sheet then put your code in that sheet. Now save the file as a Template. Now simply record a macro adding yout Template Worsheet (complete with code) to any Workbook. Use this code in your project.
Dave
OzGrid Business Applications
Posted by Paul on April 18, 2001 12:43 PM
Thanks mucho, Dave! The code you provided is a great help. As for one of the things I want to accomplish, that is prevent the user from deleting the temporary worksheet by any means other than the button I provide, I still haven't come up with a way to do this. I provide a msgbox alert after the new sheet is inserted to advise them not to, but human nature being what it is...
Am I correct in thinking that I'll have to write code to the CommandBars collection object and change the OnAction property to run a macro providing a message instead of executing the "Delete Sheet"?
'~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~~=~ Dim VBCodeModule As VBIDE.CodeModule Dim Wsht As Worksheet Dim LNum As Long 'Add a new worksheet to the active workbook _ and set it to a Worsheet variable. Set Wsht = ActiveWorkbook.Worksheets.Add 'Set a reference to the event module of the new sheet Set VBCodeModule = _ Wsht.Parent.VBProject.VBComponents(Wsht.Name).CodeModule 'Creat the new sheet event procedure(Sheet_Activate) LNum = VBCodeModule.CreateEventProc("Activate", "Worksheet") 'Write some code into the module VBCodeModule.InsertLines LNum + 1, "MsgBox ""I'm a new sheet!""" VBCodeModule.InsertLines LNum + 2, "MsgBox ""What do you think?!""" 'Release memory Set VBCodeModule = Nothing Set Wsht = Nothing
