Hi everyone,
I would like to create a sub in a new Module that, when run, will delete another particular Sub or Private Sub located in ThisWorkbook, is that doable? Ideally I can refer to the sub I want to delete by its name, rather than play with lines # in the code (because its position in the code and its size (# of lines) will evolve with time).
Thanks
Hello @MagnusNovak. A similar issue was resolved here. If you only need to remove the code from the ThisWorkbook module, then here is the option for you.Good luck.
Please Login or Register to view this content.
NOTE: As the original poster/owner, only you can mark your thread as SOLVED (Thread Tools above Post #1).
You can say "Thanks" in your thread to everyone who offered to help you.
You can also reward them by clicking * "Add Reputation" under their username on the left.
With Regards, MikeVol.
Thanks MikeVol. Just to understand better, this code deletes all of the code from ThisWorkbook, not just a particular module, right? My ThisWorkbook includes a lot more code than what I'm trying to delete so I need to target a particular section / sub. More particularly, I'm trying to remove the Private Sub Workbook_SheetSelectionChange, but nothing else outside of that.
Hello @MagnusNovak. Yes,According to Chip Pearson's site. We can find the procedure we need. The code was taken from there and modified to suit your needs.this code deletes all of the code from ThisWorkbookPlease Login or Register to view this content.Good luck.Objects In The VBA Extensibility Model
The following is a list of the more common objects that are used in the VBA Extensibilty object model. This is not a comprehensive list, but will be sufficient for the tasks at hand.
VBIDE
The VBIDE is the object library that defines all the objects and values that make up VBProject and the Visual Basic Editor. You must reference this library to use the VBA Extensibility objects. To add this reference, open the VBA editor, open your VBProject in the editor, and go to the Tools menu. There, choose References . In the References dialog, scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 and check that item in the list.
Last edited by MikeVol; 10-25-2023 at 05:48 AM. Reason: Update
Removing code from a module can fail for two reasons:
1. If you pass the file to another person then there is a good chance that "Trusted access to VBA project" is NOT checked (by default it is not).
2. If the project is password protected.
In my opinion, a better solution would be to create a condition at the beginning of the Workbook_SheetSelectionChange procedure. When a certain condition is met (you didn't specify at what point you would like this event not to occur, so it's hard to answer more precisely) there will be an early exit from the procedure.
Artik
Thanks a lot MikeVol, I'm far from understanding this code but it does work like a charm!
Artik, thanks for this precisions. To give some additional background, I'm trying to build a macro that "clean" my spreadsheet before release (so it clears some data in sheets, delete some temporary sheets and delete some VBA modules and functions only used during development). Once that macro is run, it auto-deletes itself and the spreadsheet is "clean" and ready for release. So it's really just a way for me to automatize a few tasks I used to do manually before every release. With MikeVol's code above, I'm now able to clear the last type of content I was previously missing (cells, sheets, modules, VBA code) so that's great!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks