Deleting Macros from sheet

WernerLouw

Board Regular
Joined
Dec 9, 2004
Messages
93
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... :cry:

Can anyone help me please... :pray:
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Legend
Joined
May 15, 2003
Messages
29,536
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 :devilish: ).

Hope you get it sorted,

Smitty
 

WernerLouw

Board Regular
Joined
Dec 9, 2004
Messages
93

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Board Regular
Joined
Dec 9, 2004
Messages
93

ADVERTISEMENT

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

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...

:oops:
:cry:

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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Board Regular
Joined
Dec 9, 2004
Messages
93
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... :devilish:

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

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...
 

Forum statistics

Threads
1,147,690
Messages
5,742,653
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top