Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to delete sheet code with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to delete sheet code with VBA
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 On 06/15/2010 05:09, michael.beckinsale wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to delete sheet code with VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete sheet code | Excel Programming | |||
Code to delete shapes each time a sheet is deactivated | Excel Programming | |||
What is the VBA code to delete a sheet without warning message? | Excel Discussion (Misc queries) | |||
Delete sheet code | Excel Programming | |||
Code to not display delete sheet alerts | Excel Programming |