really remova all VBA code from Workbook

[dezso.bolyan]

Hi!

I want to delete all VBA code from the EXCEL Workbook dinamically (so
from VBA program). I use this method:
...
On Error Resume Next
With ActiveWorkbook.VBProject
For x = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(x)
Next x
For x = .VBComponents.Count To 1 Step -1
.VBComponents(x).CodeModule.DeleteLines _
1, .VBComponents(x).CodeModule.CountOfLines
Next x
End With
On Error Goto 0
...

After that I have saved the workbook and I have opened it again, I get
the message box: "this workbook have macros. Would you like to allowe
them? ..." But there is not any more VBA code.

What need I to do, that after the deletion of VBA code, I do not get
this messege at the opening of the workbook again?

Thanks a lot!

Charlie

 

[Guest]

Prior to saving the file, insure that all modules have been deleted; also
insure that there is no code in any workshets or ThisWorkbook.

 

[NickHK]

Modules and class modules, even without code, will trigger the macro
warning.
So delete them also.

And of course userforms.

NickHK

 

[Norman Jones]

Hi Charlie,

Try using Chip Pearson's suggested code which additionally deletes the
modules:

'=============>>
Sub DeleteAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub
'<<=============

 

[Bob Phillips]

See http://www.contextures.com/xlfaqMac.html#NoMacros

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[dezso.bolyan]

Hi!

There is not any module or codes after the deletion of all VBA macros.
Theree are only Excel Objects like "sheet1", ... , "ThisWorkbook"
without any VBA code.

I tried to give manually a dummy code after the deletion and save:

Sub test()
End Sub

I saved this code. I deleted this code manually later and saved the
workbook again. Now if I open the workbook I do not get any message
about macros. It seems me, that I need to do any "refresh" (?) after I
delete all VBA codes????

Charlie

NickHK írta:

 

[NickHK]

Charlie,
Well, yes. You can't remove those worksheet or ThisWorkbook modules.
I only mean all the other modules.

NickHK
P.S. You should read the recent post in this NG from Rob Bovey, in case it
applies to your situation.
Re: Programmatically determining CODE NAME for sheet based upon Sh


Hi!

There is not any module or codes after the deletion of all VBA macros.
Theree are only Excel Objects like "sheet1", ... , "ThisWorkbook"
without any VBA code.

I tried to give manually a dummy code after the deletion and save:

Sub test()
End Sub

I saved this code. I deleted this code manually later and saved the
workbook again. Now if I open the workbook I do not get any message
about macros. It seems me, that I need to do any "refresh" (?) after I
delete all VBA codes????

Charlie

NickHK írta:

 

[SandyUK]

After a suggestion from Ron I have made extensive use of the information
on Chips site "Programming To The Visual Basic Editor" (thanks to both)
to achieve what the original post wanted but after extensive searching
of this NG and googling to death there realy is no work around that
will allow you to programatically supply the VBE password or set a VBE
password (please someone correct me if I am wrong).

My query is then, is there a way I can programatically call the VBE
password input screen so that the user inputs the password and then it
runs the code to clean up the modules etc?

The aim is to build an add in for my current employer that at the click
of a command button will delete some modules of work that I have done
for them and leave others.

Adrian

 

[Dave Peterson]

The only thing I've ever seen to unprotect a project uses SENDKEYS and that's
not something I would depend upon.

Have you thought about creating a couple of template files--each with the code
you need and then just put the worksheets/data into those templates?

 

[SandyUK]

Would that be possible in this scenario?

I have added code to our quote template to make sure its valid e.g
date, all fields filled in etc and also to make sure its secure (ish
automatic sheet level protection on close. The company is not going t
support the data validity aspect of the quotes when i leave and jus
"make do" but want to keep the "security". The quotes will expire ove
time but they would like to be able to open them and use them agai
rather than requoting so will need to be able to remove the dat
validation part of my code but keep the security i was looking t
produce an add in that would do this but ran into the project passwor
issue. Not 100% sure how your suggestion would work but i can sort o
see the edges of it could you give me an advice?

Adria

 

[Dave Peterson]

I don't have much to add.

If you need a workbook that needs to have a subset of macros in it, build that
as a template.

If you need code behind worksheets, either copy the worksheets (with the code)
or build worksheets with the code behind them in the template--and then just
copy|paste special (values, formulas, formats???) with events turned off.

Not knowing what needs to be done, could you provide them with that addin that
did all the validity checks (kind of in batch mode). They would have to be
trusted to run the macros, though.

 

[SandyUK]

Thanks for the suggestion and comments Dave.

I am going to try your method which I think is the best way of
achieving what needs to be done.

All the best

Adrian