Deleting Macros from sheet

[WernerLouw]

Hi,

I had a look at a long discussion on deleting macros from a work book. I now have this problem... The code discussed works fine in the modules but I need to delete the code in one of the worksheets.

Here is the code as discussed:

Public Sub DeleteAllVBA()
For Each book In Workbooks
If book.Name <> ThisWorkbook.Name Then
For Each VBComp In book.VBProject.VBComponents
With VBComp
If .Type = 100 Then
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
book.VBProject.VBComponents.Remove VBComp
End If
End With
Next VBComp
End If
book.Close SaveChanges:=True
Next book
End Sub

The original code was supplied by Firefytr which then was manipulated by Chitosunday.

This is a bit out of my league but I need to delete te code to protect my work. People are plagerising my work and there is nothing I can do about it, except maybe quit my job...

Can anyone help me please... ray:

 

[Chitosunday]

The code above will delete the code of all modules and even the sheet and worksheet code except those on thisworkbook file(where this macro was executed). How do want it revised?

 

[Chitosunday]

Are you opening a file with an open event? use the application.enableevents=false to stop the annoying event macro like this

Public Sub DeleteAllVBA()
Application.EnableEvents = False
For Each book In Workbooks
If book.Name <> ThisWorkbook.Name Then
For Each VBComp In book.VBProject.VBComponents
With VBComp
If .Type = 100 Then
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
book.VBProject.VBComponents.Remove VBComp
End If
End With
Next VBComp
End If
book.Close SaveChanges:=True
Next book
application.enableevents=true
End Sub

 

[Smitty]

This is a bit out of my league but I need to delete te code to protect my work. People are plagerising my work and there is nothing I can do about it, except maybe quit my job...

This may be a step backwards (I haven't seen your original post(s)), but have you protected your code?

In VBA-->Tools-->VBA Project Properties-->Protection-->USE A BIG PASSWORD7896537 (that you write down )...

No offense intended if you have, but if it's to that point, then I'd talk to the boss (or make EX-LAX brownies for the offenders ).

Hope you get it sorted,

Smitty

 

[WernerLouw]

There is no start up events or any annoying code popups. All I have is a calender control on a sheet with a worksheet_change sub based on the date.

I copy all the relevant or report sheets to a new workbook and then delete the macros but the macros in this specific worksheet stay... The code is not protected, and I could propably leave it as is but that would make life difficult for me since everyone would complain about the annoying message you get when you click on the calender control or you change the value of cell f6.

The problem with the plagerising is this, the boss sends these people to me so that I should help them(ID10T's). I then have to explain to them what I did in excell to get the sheet to run like this etc. etc. eventually I either have to do the work for them or my boss sends them my sheets from where they try to change the links to suit their purpose.

What I now need to do is send them a plain old excell sheet with only data. No formulas no nothing... That way nobody asks any q's about how do you do it...

The problem with working without Macros is. I cant possibly do the amount of work that i do without the macro's

 

[Chitosunday]

As i said, your original code will work even to sheet code. Did you try my second post? . If still did not work try posting all your code or email to me your file.

 

[WernerLouw]

I just realised, I deleted my personal macros workbook... ****e...


I even tried removing the code while copying. This didnt even work..

Sheets("Performance").Copy Before:=ThisWorkbook.Sheets("Sheet1")
With ActiveSheet
With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With
End With
End Sub

I figured something else out though...

I copy all the sheets without code, then I copy just the data and formats from the sheet with the code into the new wbk.I got it to work and when i wanted to use my mailing application I realised I deleted the personal macros workbook...




Any idea why the personal folder would have been deleted as well? How would I prevent this from happening again?

If book.Name <> or(ThisWorkbook.Name, personal macro workbook.xls) Then

 

[Chitosunday]

use "and" instead of or because "or" means if either one of them is true it will execute deletion. Which means 100% for or.

If book.Name <> ThisWorkbook.Name and book.name <> personal macro workbook.name Then

 

[WernerLouw]

I have found the wonderfull world of Ron De Bruin... I installed his addin and it does everything that i want to do to the letter... Maybe now the plagerists will stop bagerring me...

Or maybe not... Now they'll come and ask me to show them how I got to the values...

Thanks for your help Chitosunday. I will have a look at the code in my spare time to learn a bit and see why things did'nt want to work for me...