Is it possible to delete all modules in workbook?

[ArnMan]

Hello,

I have a work book, in Excel 2010, that uses modules to build a spreadsheet template (Datasheet-C). I work off of Sheet1. When all said and done, I have a button, on Datasheet-C, that when clicked deletes all other sheets besides Datasheet-C, removes the code from Datasheet-C for the button, and deletes the button. I have looked online in many places, but cant seem to find some way to delete all modules too.

Here is the code I have right now, that I thought was going to delete all modules, but it just deletes the code in the Datasheet-C sheet.

Private Sub DelVBA_Click()

    Dim ws As Worksheet

    With ActiveWorkbook
       For Each ws In .Worksheets 
            With .VBProject.VBComponents(ws.CodeName).CodeModule
                .DeleteLines 1, .CountOfLines
            End With

End Sub

I would like if possible to delete(remove) all the modules that are listed under modules. Many people are going to be using this, and some may not be very excel saavy.

 

[Fluff]

How about simply saving as an .xlsx file?

 

[Rick Rothstein]

I would like if possible to delete(remove) all the modules that are listed under modules. Many people are going to be using this, and some may not be very excel saavy.

I would think it would be kind of hard to remove the module your running code is in. Fluff has given you the simplest way to do what you asked for, but I am curious... what are you macros for if not to be used by the user of your workbook that you can physically delete them? Also, did you consider password protecting your code (Tools/Project Properties on the menu bar, Protection tab on the dialog box) instead of deleting it? It is not the strongest protection scheme going, but if your users are not savvy, it should be more than enough.

 

[ArnMan]

Thank you for responding quickly.

I wish I was the only one using my modules. The project I have been working on, is for work. We calibrate measuring equipment. The datasheets/templates we use are made differently for every piece of equipment we calibrate. The datasheet will tell us to set the equipment at a certain test point and we read the display and input what ever the display says into the datasheet, and it calculates and sees if that reading is in a certain range according to manufacturer's specifications.
Each datasheet has multiple rows of different test points.
And each row has a gray color too it and some cells have borders and some don't. And I've noticed when other people make these datasheets, they usually use another template and modify. A lot of times the formatting is messed up due to copy pasting, or blankcell check is around all the reading input cells, etc.

I am making this project to get rid of copy paste mistakes, or the wrong math or refs to cells that aren't even being used, and make it easier to make these datasheets as well. I would like other people to make these datasheets the same way everytime.
If you would like a copy of what I have done so far I'll be glad to let you check it out. It isn't your normal database spreadsheet. I could have a note tell the end user to save the file as a .xlsx file. I guess passwording the main file so they can't over write mine, would keep it from getting messed up.

I would think it would be kind of hard to remove the module your running code is in. Fluff has given you the simplest way to do what you asked for, but I am curious... what are you macros for if not to be used by the user of your workbook that you can physically delete them? Also, did you consider password protecting your code (Tools/Project Properties on the menu bar, Protection tab on the dialog box) instead of deleting it? It is not the strongest protection scheme going, but if your users are not savvy, it should be more than enough.

 

[Rick Rothstein]

I don't think we need to see the file, I was just curious. As for your request, you have two possible solution... save the file as an .xlsx (Fluff's suggestion) or password protect and hide the code from view (I forgot to mention that last one in my previous message), one of those should satisfy your stated needs.

 

[ArnMan]

Thank you very much you are always very helpful.