Use VB code to remove code in sheet1

  • Thread starter Thread starter WashoeJeff
  • Start date Start date
W

WashoeJeff

I can't seem to find anything that works to remove code from Sheet1 or for
that matter any modules

I have a procedure and am going to send the finished sheet but want to strip
the code with a routine I followed instructions in a book but it doesnt seem
to work.
Any help appreciated
Jeff
 
Jeff,

Try something like the following:

With ThisWorkbook
With .VBProject.VBComponents( _
.Worksheets("Sheet1").CodeName).CodeModule
.DeleteLines 1, .CountOfLines
End With
End With

See www.cpearson.com/excel/vbe.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Jeff,

Sub test()
Dim strCodeName As String

strCodeName = Worksheets("My Sheet 1").CodeName
With ThisWorkbook.VBProject.VBComponents(strCodeName).CodeModule
If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
End With
End Sub

You could have problems running this under Excel 2002 with the security
features. Recommend investigating 'trusted code' options if this is a
problem.

Rob
 
Back
Top