hatman
Well-known Member
- Joined
- Apr 8, 2005
- Messages
- 2,664
I have a workbook that has two levels of users: 1)Peon 2)Administrator. A custom toolbar gets generated when the book is activated, and the buttons that populate it depend on whether the user's name appears in the Admin list. One of these buttons allows the admin to add a new sheet, with a whole bunch of additional activities to link the new sheet with all of the others. I am currently using code in the NewSheet event to delete new sheets as they get added using any method other that my admin button. And this works VERY well.
The thing is that I WANT the admin to be able to able to add sheets, delete sheets, rename sheets or move sheets. There should be no restrictions on moving or renaming sheets, but adding or deleting sheets needs to happen through macros that handle the required set of operations.
My problem is preventing someone from deleting a sheet through an avenue other than my admin button. There is no event that seems to fire for deleting a sheet. I have tried defining a new event using the WithEvents declaration in a class module, but can't seem to locate or define the SheetDelete event.
I have toyed with protecting the workbook structure, and simply unprotecting the workbook in the macro to allow the macro to do the sheet manipulation... but that turns off the SheetRename and SheetMove options... I suppose that I can simply write 2 more macros to perform these functions after unprotecting the book programmatically, but it seems like there ought to be a more elegant solution without cluttering my toolbar with even more buttons.
Any suggestions would be greatly appreciated... perhaps I already have my solution, even though it isn't as clean as I would like, but since there always seems to be more that you guys know, I figured I'd pose the question.
The thing is that I WANT the admin to be able to able to add sheets, delete sheets, rename sheets or move sheets. There should be no restrictions on moving or renaming sheets, but adding or deleting sheets needs to happen through macros that handle the required set of operations.
My problem is preventing someone from deleting a sheet through an avenue other than my admin button. There is no event that seems to fire for deleting a sheet. I have tried defining a new event using the WithEvents declaration in a class module, but can't seem to locate or define the SheetDelete event.
I have toyed with protecting the workbook structure, and simply unprotecting the workbook in the macro to allow the macro to do the sheet manipulation... but that turns off the SheetRename and SheetMove options... I suppose that I can simply write 2 more macros to perform these functions after unprotecting the book programmatically, but it seems like there ought to be a more elegant solution without cluttering my toolbar with even more buttons.
Any suggestions would be greatly appreciated... perhaps I already have my solution, even though it isn't as clean as I would like, but since there always seems to be more that you guys know, I figured I'd pose the question.
