i have a sheet that runs a macro and then does a save as.

is it possible to delete the macro and command button that launches the
routine before the save as? i didn't think so because the code is still
running.

--


Gary

Gary:

try,

Sub deletecode()
Dim objVBC As Object
Dim objMdl As Object
Dim arr() As Variant
Dim intCounter As Integer
Dim txt As String, fileSaveName As Variant
Dim msg As String
Dim ctl As Shape
'save as
msg = "Save as file"
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", Title:=msg)
If fileSaveName = False Then Exit Sub
Application.ScreenUpdating = False
'delete Sheet1 shape
For Each ctl In Sheet1.Shapes
ctl.Delete
Next ctl
'Star del code
ReDim arr(1 To 3, 1 To ActiveWorkbook.VBProject.VBComponents.Count)
intCounter = 0
For Each objVBC In ActiveWorkbook.VBProject.VBComponents
Set objMdl = objVBC.CodeModule
intCounter = intCounter + 1
arr(1, intCounter) = objVBC.Type
arr(2, intCounter) = objVBC.Name
If objMdl.countoflines 0 Then
txt = objVBC.CodeModule.Lines(1, objMdl.countoflines)
End If
arr(3, intCounter) = txt
Select Case arr(1, intCounter)
Case 1 '(Module)
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
Case 2 ' (Class1)
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
Case 100 ' Thisworkbook
objVBC.CodeModule.DeleteLines 1, objMdl.countoflines
Case 3 'UserForm
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
DoEvents
End Select
Next objVBC
ThisWorkbook.SaveAs fileSaveName
Application.ScreenUpdating = True
End Sub


--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Gary Keramidas" wrote:

i have a sheet that runs a macro and then does a save as.

is it possible to delete the macro and command button that launches the
routine before the save as? i didn't think so because the code is still
running.

--


Gary

thanks, cj, i'll give it a shot. already have the save routing, just looking
to delete a command button and the macro it runs.


--


Gary


"chijanzen" wrote in message
...
Gary:

try,

Sub deletecode()
Dim objVBC As Object
Dim objMdl As Object
Dim arr() As Variant
Dim intCounter As Integer
Dim txt As String, fileSaveName As Variant
Dim msg As String
Dim ctl As Shape
'save as
msg = "Save as file"
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", Title:=msg)
If fileSaveName = False Then Exit Sub
Application.ScreenUpdating = False
'delete Sheet1 shape
For Each ctl In Sheet1.Shapes
ctl.Delete
Next ctl
'Star del code
ReDim arr(1 To 3, 1 To ActiveWorkbook.VBProject.VBComponents.Count)
intCounter = 0
For Each objVBC In ActiveWorkbook.VBProject.VBComponents
Set objMdl = objVBC.CodeModule
intCounter = intCounter + 1
arr(1, intCounter) = objVBC.Type
arr(2, intCounter) = objVBC.Name
If objMdl.countoflines 0 Then
txt = objVBC.CodeModule.Lines(1, objMdl.countoflines)
End If
arr(3, intCounter) = txt
Select Case arr(1, intCounter)
Case 1 '(Module)
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
Case 2 ' (Class1)
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
Case 100 ' Thisworkbook
objVBC.CodeModule.DeleteLines 1, objMdl.countoflines
Case 3 'UserForm
ActiveWorkbook.VBProject.VBComponents.Remove objVBC
DoEvents
End Select
Next objVBC
ThisWorkbook.SaveAs fileSaveName
Application.ScreenUpdating = True
End Sub


--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Gary Keramidas" wrote:

i have a sheet that runs a macro and then does a save as.

is it possible to delete the macro and command button that launches the
routine before the save as? i didn't think so because the code is still
running.

--


Gary