programatically deleted code still triggers macro warning

[Ken]

I am using code posted by Chip Pearson to programatically delete a
button and its associated code. The file from which the button and
code are deleted is then saved and automatically inserted into an e-
mail. When the attached file is opened directly from the e-mail
(which the receiving customer will necessarily do) the macro warning
still appears. The button code appears to be gone and when I try to
clean the project with Rob Bovey's slick code cleaner, the file from
which I have deleted the code does not show up as an option, which I
believe indicates that there is no code. Also, if I save the file and
reoopen it, I do not get the macro warning on the second opening.

The code I am using is to delete the code is:

Set WS = Workbooks(nWB).Worksheets("Quote")

With WS
.OLEObjects(CommandButtonName).Delete
Set CodeMod =
Workbooks(nWB).VBProject.VBComponents(.CodeName).CodeModule
End With

With CodeMod
.DeleteLines StartLine:=1, Count:=.CountOfLines
End With

When the whole routing is completed the button is gone and the code no
longer appears; but, I get the macro warning.

Is there something else I need to add to make it understand that once
this is accomplished there is no more code and therefore no need to
prompt the user with the macro warning?

Thanks

Ken

 

[Bob Phillips]

You need to delete the module as well.

Dim vbProj As Object
Dim vbMod As Object

Set vbProj = ActiveWorkbook.VBProject
Set vbMod = vbProj.VBComponents("Module3")
vbProj.VBComponents.Remove vbMod


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ken]

Bob

The code is attached to a commandbutton from the control toolbox on a
worksheet, so the code is actually in the Sheet2 Object, so I don't
think I can delete the module. Also, opening and closing the file
manually once takes care of the problem without deleting anything
further. I have closed and reopened the file via VBA, but, the first
time the file is opened using file-open keyboard commands, the macro
warning is displayed. Since the purpose of the button is to automate
some file sanitization efforts prior to e-mailing to a customer, I
would like the opening of the file, as attached to the e-mail, not to
trigger the macro warning.

I guess I could switch to a button from the forms toolbar, so the code
will be in a regular module, and then delete the module, like you
pointed out.

Thanks for your time on this posting as well as your many many other
informative contributions.

Ken

 

[Ken]

Upon further review, a forms toolbar button has the added benefit of
not having the code in the worksheet object, so it does not get copied
to the new workbook when the sheet is copied; so I don't need to
delete any code or any modules. I like that. I will need to use my
new ability to delete code and modules somewhere else.

 

[Bob Phillips]

Well I am glad I helped ( meaning I watched on as you solved it <vbg>)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)