Delete All VBA

[IcePirate]

Hello, this topic somewhat relates to this topic:
http://www.vbaexpress.com/forum/showthread.php?t=26549

What Im trying to do now is after a user saves his/her document, and X's out of the word document (closes the Word document) I want all VBA code to delete from the VBE.

Is that possible? Once again here is my code:
(Also this thread is cross-posted here: (for additional feedback) http://www.tek-tips.com/viewthread.cfm?qid=1546538&page=1 )



Sub AutoOpen()
Dim myWB As Excel.Workbook
Dim TempStr As String
Dim intRows As Integer
Dim intRecords As Integer
Dim intProjects As Integer
Dim intCount As Integer
Dim strEmployee(0 To 1000) As String
Set myWB = GetObject("X:\2009 Core Projects.xls")
Selection.GoTo What:=wdGoToBookmark, Name:="first_mark"
TempStr = "Start"
intRows = 3
'Get all projects
Do While TempStr <> ""
intRows = intRows + 1
TempStr = myWB.Sheets("Sheet1").Cells(intRows, 3)
Loop
intRows = intRows - 1
intProjects = intRows
'Get all employees
For intRecords = 3 To intRows
If (myWB.Sheets("Sheet1").Cells(intRecords, 9) = "Not Started" Or myWB.Sheets("Sheet1").Cells(intRecords, 3) = "") Then
Else
strEmployee(intRecords) = myWB.Sheets("Sheet1").Cells(intRecords, 5)
End If
Next
'List employees and projects in Word Document
For intRecords = 3 To intRows
If strEmployee(intRecords) <> "" Then
'Has this employee already been entered into the Word Report?
For intCount = 3 To intRecords - 1
'Already in Word Report - Move to next employee
If strEmployee(intRecords) = strEmployee(intCount) Then GoTo Reported
Next
'Not In Word Report - Include in Document
Selection.TypeText strEmployee(intRecords)

'Bold Names
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Font.Bold = wdToggle
Selection.EndKey Unit:=wdLine
Selection.TypeText vbCr
Selection.Font.Bold = wdToggle

For intCols = 1 To intProjects
If myWB.Sheets("Sheet1").Cells(intCols, 5) = strEmployee(intRecords) Then
If myWB.Sheets("Sheet1").Cells(intCols, 9) <> "Not Started" Then
Selection.TypeText myWB.Sheets("Sheet1").Cells(intCols, 3) & vbCr
End If
End If
Next
'Spacer to next employee
Selection.TypeText vbCr
Reported:
End If
Next
Set myWB = Nothing
End Sub
Private Sub CommandButton1_Click()
Dim Today, FolderName$, DateValue$, NameofFile$, FullFileName$
FolderName$ = "X:\Weekly Meetings\"
DateValue$ = Format(Now, "yyyy-mm-dd")
NameofFile$ = "- Core Agenda"
FullFileName$ = FolderName$ + DateValue$ + " " + NameofFile$
ActiveDocument.SaveAs FileName:=FullFileName$, FileFormat:=wdFormatDocument
End Sub

[lucas]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=64

I use a global template instead of normal to hold code like this but you can use it from normal.dot

Another method you might try but it is designed for Excel. Should be easy to reconfigure:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=93

change the activeworkbook parts to activedocument.

[IcePirate]

Hey Lucas,

I just wanted to say thanks for your help! But Im having some troubles.

1. If I create this as an add-in, for some reason its not showing up in my Add-In manager.

2. I was able to reconfigure the one code it worked, as well as the first code, they both worked when I tried them out, so thanks again! :). But there is still the issue of not being able to figure out how to combine the two scripts (Ie, the command button script, and the delete all VBA script), Ive made several attempts to combine the two so immediately after the command button is clicked and the document is saved, all VBA is removed.

Thanks again for all your help, Im going to continue to mess with the add-in manager, see if I can get it working properly.


http://www.vbaexpress.com/kb/getarticle.php?kb_id=64

I use a global template instead of normal to hold code like this but you can use it from normal.dot

Another method you might try but it is designed for Excel. Should be easy to reconfigure:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=93

change the activeworkbook parts to activedocument.

[lucas]

1. If I create this as an add-in, for some reason its not showing up in my Add-In manager.

You need to save it as a .dot or template file and it needs to be saved in your startup directory. See tools-options-file locations.

[fumei]

As I responded in the Tek-Tips thread, and as Steve (lucas) has mentioned, if your code was in a global template (a code container), then you could execute the code procedures and the user would never be able to see the code. In the VBE it would be "Project unviewable".