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

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


" wrote:

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

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

And of course userforms.

NickHK

wrote in message
oups.com...
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

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:
Modules and class modules, even without code, will trigger the macro
warning.
So delete them also.

And of course userforms.

NickHK

wrote in message
oups.com...
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

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.
Programmatically determining CODE NAME for sheet based upon Sh


wrote in message
ups.com...
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:
Modules and class modules, even without code, will trigger the macro
warning.
So delete them also.

And of course userforms.

NickHK

wrote in message
oups.com...
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

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


--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=571301

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 wrote:

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

--
SandyUK
------------------------------------------------------------------------
SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
View this thread: http://www.excelforum.com/showthread...hreadid=571301

--

Dave Peterson

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
'<<=============


---
Regards,
Norman



wrote in message
oups.com...
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

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

--
HTH

Bob Phillips

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

wrote in message
oups.com...
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