How do I delete VBA code from a sheet using VBA?

Question

I want to delete the VBA code contained within a sheet through VBA. Currently, I have a code that copies a sheet across to a new workbook and deletes all images from it. However, these images are set to do things on Worksheet_Activate in the code, which then causes an error whenever I flick to that sheet with no images there.

I know I can get rid of modules etc using something along the lines of:

 With ActiveWorkbook.VBProject For x = .VBComponents.Count To 1 Step -1 .VBComponents.Remove .VBComponents(x) Next x For x = .VBComponents.Count To 1 Step -1 .VBComponents(x).CodeModule.DeleteLines _ 1, .VBComponents(x).CodeModule.CountOfLines Next x End With

but that does not delete from the sheet (or the workbook for that matter. Would be interesting to know if that was possible too).

The code itself will need to be valid for Excel versions 2003 through to 2013 (so cannot use the save as xlsx workaround).

Why not save the file as an xlsx File? The code will be automatically deleted. Also if I am not wrong, I might have answered a similar question earlier — Siddharth Rout
– Siddharth Rout, Commented Nov 26, 2013 at 16:49
before flicking you may want to do Application.EnableEvents = False to temporarily disable events and do what you need to do. The approach you're going for now should be your last resource really.. — user2140173
– user2140173, Commented Nov 26, 2013 at 16:50
lol@nixda: That one and there was another one... But that was fast :P — Siddharth Rout
– Siddharth Rout, Commented Nov 26, 2013 at 16:53

Nimantha · Accepted Answer · 2021-09-17 05:43:59Z

Found out the issue. My code works fine, just the computer I was testing it on did not allow access to the VBA project object model (and was running it with an On Error Resume Next earlier on in the code)

Will have to write an exception in to sort that out in such cases.

Thanks to @mehow @nixda @SiddharthRout and @DaveU for the help

Collectives™ on Stack Overflow

How do I delete VBA code from a sheet using VBA?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related