Macro warning with no macros

[clemensma]

I know this question as been asked many times in the past and I have
searched for a fix for my situation but haven't found one that works.
Here's what's happening:

I have a workbook that does have lots of macros and is an XLSM file.
One of the macros copies one of the worksheets (doesn't have any
macros) to a new workbook. It then updates the data in the new
workbook and finally saves it using an "XLS" prefix. When I open the
new workbook I get the macro warning. I tried the following code
before the save to get rid of any macros in the new workbook and while
it does find 2 VBE component it doesn't get rid of the macro warning.

With ActiveWorkbook.VBProject
For i = .VBComponents.Count To 1 Step -1
.VBComponents(i).CodeModule.DeleteLines
1, .VBComponents(i).CodeModule.CountOfLines
Next i

For i = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(i)
Next i
End With

Is there anything else I can do to get rid of the macro warning from
the new workbook? I know I could create a new workbook instead of
coping the sheet but I'd like to preserve all of the formatting,
headers and trailers without having to add setting them in the macro.

Thanks for your help.

 

[James Ravenswood]

I know this question as been asked many times in the past and I have
searched for a fix for my situation but haven't found one that works.
Here's what's happening:

I have a workbook that does have lots of macros and is an XLSM file.
One of the macros copies one of the worksheets (doesn't have any
macros) to a new workbook.  It then updates the data in the new
workbook and finally saves it using an "XLS" prefix.  When I open the
new workbook I get the macro warning.  I tried the following code
before the save to get rid of any macros in the new workbook and while
it does find 2 VBE component it doesn't get rid of the macro warning.

    With ActiveWorkbook.VBProject
        For i = .VBComponents.Count To 1 Step -1
            .VBComponents(i).CodeModule.DeleteLines
1, .VBComponents(i).CodeModule.CountOfLines
        Next i

        For i = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(i)
        Next i
    End With

Is there anything else I can do to get rid of the macro warning from
the new workbook?  I know I could create a new workbook instead of
coping the sheet but I'd like to preserve all of the formatting,
headers and trailers without having to add setting them in the macro.

Thanks for your help.

After running your posted code, verify:

1. all the subs are gone
2. all the modules that contained the subs are gone
3. all worksheet code is gone
4. all workbook code is gone

The problem is probably in the 2

 

[clemensma]

After running your posted code, verify:

1. all the subs are gone
2. all the modules that contained the subs are gone
3. all worksheet code is gone
4. all workbook code is gone

The problem is probably in the 2

Thanks for the suggestion. I had already checked all of those areas
and I don't see any code. There aren't any module files and there's
no visible code in either the worksheet or workbook.

 

[James Ravenswood]

Thanks for the suggestion.  I had already checked all of those areas
and I don't see any code.  There aren't any module files and there's
no visible code in either the worksheet or workbook.- Hide quoted text -

- Show quoted text -

If you don't receive a solution here, I suggest you repost your
question here:

http://social.answers.microsoft.com/Forums/en-US/officeprog/threads

 

[Jon Peltier]

ActiveX controls embedded in the worksheet may give this warning.

- Jon