Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 691
Default Code & modules are gone, Macro warning message remains

Hy Byron,
Take a look at Chip Pearson's
Deleting All VBA Code In A Project
http://www.cpearson.com/excel/vbe.htm

If he missed something, I'm sure he would be interested.
But you will see that it deletes Standard Modules, Forms,
Class Modules as well as emptying sheets of code.

I believe you did not remove the forms.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Byron" wrote in message ...
I have an Excel Workbook where users can fill out a form and then press a
command button on the Sheet that saves a copy of the file into a pre-defined
directory. I don't want the copied version of the Workbook to have any of the
VBA code or the command button in it so I tried to remove them using the
following code inside the copy subroutine:
...
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
.DeleteLines 1, .CountOfLines
End With
ActiveWorkbook.ActiveSheet.OLEObjects("My_Button") .Delete
...
The code and the button are removed just fine from the copied file, but when
I open the new copy of the Workbook, Excel gives me the Macro warning
message. I have checked in the Visual Basic Editor and there are no
additional modules attached to the Workbook and there is no code in any of
the Sheet modules or the ThisWorkbook module.

How can I completely get rid of the button and code so that I no longer get
the Macro warning message?

Thanks in advance.



  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Code & modules are gone, Macro warning message remains

Hi David,

I don't have any forms in the original workbook, just two command buttons on
one of the sheets and some code in the module for that sheet. When I press
one of the buttons, the code runs and at the end I am left with excel open
and the new copy of the workbook showing. I exit out of excel completely, and
then navigate in Windows Explorer to the new file (which wasn't there
before). I open the file (and therefore excel) from there and that is when I
get the Macro warning. Then I dismiss the warning, save the document without
making any changes, exit excel and then re-open the doucment and the warning
is gone. Seems really bizarre to me, so if you have any other suggestions,
let me know. Thanks.

My system:
Windows XP Pro (SP 1)
Microsoft Office Professional Edition 2003 (SP 1)


"David McRitchie" wrote:

Hi Byron, (posted reply to programming)
Mystery to me. Chip would have written this originally in
Excel 2000. So many people use it that I can't imagine it not
being good for later versions as well. Forms are certainly
deleted, but it might not be a common practice to delete
macros if you have forms.

Are you exiting Excel, or just the workbook. If just
exiting the workbook, try exiting Excel, then reopening
the workbook. If that works don't ask me what difference
it would make, but of course, your feedback would be needed.

How are you opening the file, can you verify that you are
opening the file you think you are opening by going down
through the directories from "My Computer". (if a PC).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Byron" wrote in message ...
Thanks for you response David,

I adapted my code from some I found on Chip Pearson's site in the first
place. I modified my code to use his exact DeleteAllVBA function (after your
suggestion) with a small modification, and I still have the same problem.
Weird thing is, if I open the copied excel file the first time, it gives me
the macro warning. If I choose to accept macros and then save the file
without making any other changes, the next time I open it up the warning is
gone. It's like when saving it as a user includes some extra check that
saving it programmatically does not. I really would like the copied file to
not have the warning message, so any more suggestions would be appreciated.
Thanks.


Here is the code as it stands right now:

Public Sub CreateCopy()
' save a copy of excel workbook
ThisWorkbook.SaveAs FileName:=("C:\Temp\" + ThisWorkbook.Name)

' delete buttons from new version

ActiveWorkbook.ActiveSheet.OLEObjects("GenerateDoc umentsEnglish_Button").Delete

ActiveWorkbook.ActiveSheet.OLEObjects("GenerateDoc umentsFrench_Button").Delete

' remove code from new version of the pricing spreadsheet
DeleteAllVBA ActiveWorkbook

' delete export worksheet
Application.DisplayAlerts = False
ActiveWorkbook.Unprotect
ActiveWorkbook.Sheets("Export").Delete
ActiveWorkbook.Protect
Application.DisplayAlerts = True

' save changes
ActiveWorkbook.Save
End Sub

' following code adapted from http://www.cpearson.com/excel/vbe.htm
Sub DeleteAllVBA(TargetBook As Excel.Workbook)

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = TargetBook.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



"David McRitchie" wrote:

Hy Byron,
Take a look at Chip Pearson's
Deleting All VBA Code In A Project
http://www.cpearson.com/excel/vbe.htm

If he missed something, I'm sure he would be interested.
But you will see that it deletes Standard Modules, Forms,
Class Modules as well as emptying sheets of code.

I believe you did not remove the forms.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Byron" wrote in message ...
I have an Excel Workbook where users can fill out a form and then press a
command button on the Sheet that saves a copy of the file into a pre-defined
directory. I don't want the copied version of the Workbook to have any of the
VBA code or the command button in it so I tried to remove them using the
following code inside the copy subroutine:
...
With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
.DeleteLines 1, .CountOfLines
End With
ActiveWorkbook.ActiveSheet.OLEObjects("My_Button") .Delete
...
The code and the button are removed just fine from the copied file, but when
I open the new copy of the Workbook, Excel gives me the Macro warning
message. I have checked in the Visual Basic Editor and there are no
additional modules attached to the Workbook and there is no code in any of
the Sheet modules or the ThisWorkbook module.

How can I completely get rid of the button and code so that I no longer get
the Macro warning message?

Thanks in advance.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Warning Message Tendresse Excel Discussion (Misc queries) 0 February 29th 08 08:44 AM
Macro Warning Message Lisa Neron Excel Discussion (Misc queries) 5 January 16th 08 02:27 AM
Stoping the Macro Warning Message Jurrasicway Excel Discussion (Misc queries) 5 June 19th 06 02:56 PM
What is the VBA code to delete a sheet without warning message? [email protected] Excel Discussion (Misc queries) 2 August 9th 05 04:16 PM
getting a macro warning, even through there is no code Sandy[_5_] Excel Programming 4 November 15th 03 04:15 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"