Post formatting; removed fluff

edited Sep 17, 2021 at 6:10

6.5k
6
32
78

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

I am wantingwant 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)

Thanks in advance.

Ben

Delete VBA code from a sheet using VBA

I am wanting 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)

Thanks in advance.

Ben

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

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

added 126 characters in body

Source Link

edited Nov 27, 2013 at 8:48

bmgh1985

789
1
15
38

I am wanting 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)

Thanks in advance.

Ben

I am wanting 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)

Thanks in advance.

Ben

I am wanting 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)

Thanks in advance.

Ben

Rollback to Revision 1

Source Link

edited Nov 27, 2013 at 8:38

bmgh1985

789
1
15
38

Delete VBA code from a sheet using VBA

How do I am wanting to delete the VBA code contained within a sheet module usingthrough 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 regular 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

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

Thanks in advance. How do I do this?

Ben

Delete code from a sheet using VBA

How do I delete the VBA code contained within a sheet module using 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 regular modules 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 code from sheets (or from the workbook for that matter). How do I do this?

Delete VBA code from a sheet using VBA

I am wanting 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)

Thanks in advance.

Ben