D
decc
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
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