Trying to delete sheet code with VBA

[michael.beckinsale]

Hi All,

Glad this newsgroup still appears to be working!

I am building a consolidation type workbook that imports specific
sheets from all workbooks in a folder. All of the sheets to be
imported have Worksheet_Activate events coded into them and l need to
remove the code either at the time of copying the sheet into the
consolidator or by looping through each sheet and if the tab color
index is 24 then delete the code.

Below is some sample code which seems to work in a test file but bombs
out in the consolidator file on the Application.VBE...... line. I have
set a reference to the VBA Extensibilty 5.3 and am using XL2003

I have tried many different variations but l just can't seem to get it
quite right! Any help would be gratefully appreciated.



Sub TestDeleteVBA()

Dim Filename As String
Filename = ActiveWorkbook.Name

For Each Sht1 In Workbooks(Filename).Worksheets
If Sht1.Tab.ColorIndex = 24 Then
Sht1.Activate
With
Application.VBE.ActiveVBProject.VBComponents(Sht1.Name).CodeModule
.DeleteLines StartLine:=1, Count:=.CountOfLines
End With
End If
Next Sht1

End Sub

Regards

Michael

 

[Dave Peterson]

You need to refer to the .codename of the sheet, not the sheet:

Option Explicit
Sub TestDeleteVBA()

Dim sht1 As Worksheet

For Each sht1 In ActiveWorkbook.Worksheets
If sht1.Tab.ColorIndex = 24 Then
With Application.VBE.ActiveVBProject _
.VBComponents(sht1.CodeName).CodeModule
.DeleteLines StartLine:=1, Count:=.CountOfLines
End With
End If
Next sht1

End Sub


And remember, you (and every user who runs this code) will need to allow your
program access the Visual basic project:

In xl2003 menus:
Tools|Macro|Security|trusted publishers
Check the bottom checkbox

 

[michael.beckinsale]

Hi Dave,

Thanks very much, its so simple when you know how!

I was pretty sure that there wasn't too much wrong with my code but
you know how it is sometimes when you just cant see the wood for the
tree's.

Regards

Michael