Better way to delete macros & other components???

[[email protected]]

I'm a newbie at this so please be tolerant of the incorrect terminology
as I try to explain what I need. I have created a purchase order form
in Excel 97 using macros for automatic numbering, data verification,
printing, saving, etc. Not knowing how to incorporate a database for
data entry I created the worksheet three pages long which covers orders
from 1 to 60 items in size. On each page I imbedded graphics (logos)
and created a "Save" button and "Print" button which execute a data
verification macro before saving or printing. Each page also has a
"Help" button. The "Save" button automatically assigns a filename and
saves the file to our file server for permanent storage. The saved
files don't need the buttons or macros so I decided to delete any
unused pages, remove all buttons and delete all macros. The problem is
that it is taking a long time to save a file. I'm looking for better
ways to do the clean-up. Any suggestions would be appreciated.

Here is what I am doing...

For deleting buttons I use:
ActiveSheet.Shapes("Button 10").Select
Selection.Cut
I have 12 of these.

For deleting extra pages I check the first data cell of each page and
delete the page if that cell is blank as follows:
===code starts===
If Range(strP2DataCell1) = "" Then
If Range(strP2DataCell2) = "" Then
intCounter = strP2FirstRow
Do Until intCounter > strP3LastRow
Worksheets(1).Rows(strP2FirstRow).Delete
intCounter = intCounter + 1
Loop
' Delete embedded MS Word Objects (DECC logo on pages 2 and 3)
ActiveSheet.Shapes("Object 36").Select
Selection.Cut
ActiveSheet.Shapes("Object 43").Select
Selection.Cut
End If
'If the first two "Quantity" fields on Page 3 are blank then delete all
'rows that make up page 3
ElseIf Range(strP3DataCell1) = "" Then
If Range(strP3DataCell2) = "" Then
intCounter = strP3FirstRow
Do Until intCounter > strP3LastRow
Worksheets(1).Rows(strP3FirstRow).Delete
intCounter = intCounter + 1
Loop
' Delete embedded MS Word Objects (DECC logo on pages 3)
ActiveSheet.Shapes("Object 43").Select
Selection.Cut
End If
End If
===code ends===

For deleting macros I use code that probably came from help from this
group.<g> This is not my code:
===code begins===
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
===code ends===

Much thanks,

BrianG

[Tom Ogilvy]

Inline

<[email protected]> wrote in message
news:[email protected]...
> I'm a newbie at this so please be tolerant of the incorrect terminology
> as I try to explain what I need. I have created a purchase order form
> in Excel 97 using macros for automatic numbering, data verification,
> printing, saving, etc. Not knowing how to incorporate a database for
> data entry I created the worksheet three pages long which covers orders
> from 1 to 60 items in size. On each page I imbedded graphics (logos)
> and created a "Save" button and "Print" button which execute a data
> verification macro before saving or printing. Each page also has a
> "Help" button. The "Save" button automatically assigns a filename and
> saves the file to our file server for permanent storage. The saved
> files don't need the buttons or macros so I decided to delete any
> unused pages, remove all buttons and delete all macros. The problem is
> that it is taking a long time to save a file. I'm looking for better
> ways to do the clean-up. Any suggestions would be appreciated.
>
> Here is what I am doing...
>
> For deleting buttons I use:
> ActiveSheet.Shapes("Button 10").Select
> Selection.Cut
> I have 12 of these.

Activesheet.Buttons.Delete

>
> For deleting extra pages I check the first data cell of each page and
> delete the page if that cell is blank as follows:
> ===code starts===
> If Range(strP2DataCell1) = "" Then
> If Range(strP2DataCell2) = "" Then
> intCounter = strP2FirstRow
> Do Until intCounter > strP3LastRow
> Worksheets(1).Rows(strP2FirstRow).Delete
> intCounter = intCounter + 1

Don't you know how many rows there are
Rows(strP2FirstRow).Resize(strP3LastRow-strP2FirstRow+1).Delete


> Loop
> ' Delete embedded MS Word Objects (DECC logo on pages 2 and 3)
> ActiveSheet.Shapes("Object 36").Select
> Selection.Cut
> ActiveSheet.Shapes("Object 43").Select
> Selection.Cut
> End If
> 'If the first two "Quantity" fields on Page 3 are blank then delete all
> 'rows that make up page 3
> ElseIf Range(strP3DataCell1) = "" Then
> If Range(strP3DataCell2) = "" Then
> intCounter = strP3FirstRow
> Do Until intCounter > strP3LastRow
> Worksheets(1).Rows(strP3FirstRow).Delete
> intCounter = intCounter + 1

Same concept as above

> Loop
> ' Delete embedded MS Word Objects (DECC logo on pages 3)
> ActiveSheet.Shapes("Object 43").Select
> Selection.Cut
> End If
> End If
> ===code ends===
>
> For deleting macros I use code that probably came from help from this
> group.<g> This is not my code:
> ===code begins===
> If objDocument Is Nothing Then Exit Sub
> i = 0
> On Error Resume Next
> i = objDocument.VBProject.VBComponents.Count
> On Error GoTo 0
> If i < 1 Then ' no VBComponents or protected VBProject
> MsgBox "The VBProject in " & objDocument.Name & _
> " is protected or has no components!", _
> vbInformation, "Remove All Macros"
> Exit Sub
> End If
> With objDocument.VBProject
> For i = .VBComponents.Count To 1 Step -1
> On Error Resume Next
> .VBComponents.Remove .VBComponents(i)
> ' delete the component
> On Error GoTo 0
> Next i
> End With
> With objDocument.VBProject
> For i = .VBComponents.Count To 1 Step -1
> l = 1
> On Error Resume Next
> l = .VBComponents(i).CodeModule.CountOfLines
> .VBComponents(i).CodeModule.DeleteLines 1, l
> ' clear lines
> On Error GoTo 0
> Next i
> End With
> ===code ends===
>
> Much thanks,
>
> BrianG
>
--
Regards,
Tom Ogilvy