Deleting code in a file with code..
 

Hi ,
I am running this bit of code below from an addin file that deletes the code
in Sheet 1 of the active workbook.
It works fine..except for the Save part.
After I run this codes and file closes... I open the file back up and it
prompts to enable macros... There are no macros in the workbook anymore (i
checked)... and if I click on the Save button in Excel, close that file, and
then open it up again.. it doesn't prompt to enable macros..
I need to be able to run this code.. have the file save and close and then
when it's opened back up.. the enable macro message will not appear
..
The only way I have been able to get this to work is by taking out the
"activeworkbook.close" section of the code and then..after the code runs.. I
click the Save button in Excel and then close it myself.

Does anyone know why this is happening???




If ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule.CountofLines
0 Then
With ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule
.DeleteLines 1, .CountofLines
End With
Else
MsgBox prompt:="No Code to Delete!"


End If
Activeworkbook.Save
Activeworkbook.Close true

End Sub

Thanks in advance for your help...
Kimberly

Deleting code in a file with code..
 

Kimberly

It worked for me as expected. What version of Excel/Windows are you using.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"KimberlyC" wrote in message
...
Hi ,
I am running this bit of code below from an addin file that deletes the
code
in Sheet 1 of the active workbook.
It works fine..except for the Save part.
After I run this codes and file closes... I open the file back up and it
prompts to enable macros... There are no macros in the workbook anymore (i
checked)... and if I click on the Save button in Excel, close that file,
and
then open it up again.. it doesn't prompt to enable macros..
I need to be able to run this code.. have the file save and close and then
when it's opened back up.. the enable macro message will not appear
.
The only way I have been able to get this to work is by taking out the
"activeworkbook.close" section of the code and then..after the code runs..
I
click the Save button in Excel and then close it myself.

Does anyone know why this is happening???




If ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule.CountofLines

0 Then
With ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule
.DeleteLines 1, .CountofLines
End With
Else
MsgBox prompt:="No Code to Delete!"


End If
Activeworkbook.Save
Activeworkbook.Close true

End Sub

Thanks in advance for your help...
Kimberly

Deleting code in a file with code..
 

I'm using Excel 2000


"Dick Kusleika" wrote in message
...
Kimberly

It worked for me as expected. What version of Excel/Windows are you
using.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"KimberlyC" wrote in message
...
Hi ,
I am running this bit of code below from an addin file that deletes the
code
in Sheet 1 of the active workbook.
It works fine..except for the Save part.
After I run this codes and file closes... I open the file back up and it
prompts to enable macros... There are no macros in the workbook anymore
(i
checked)... and if I click on the Save button in Excel, close that file,
and
then open it up again.. it doesn't prompt to enable macros..
I need to be able to run this code.. have the file save and close and
then
when it's opened back up.. the enable macro message will not appear
.
The only way I have been able to get this to work is by taking out the
"activeworkbook.close" section of the code and then..after the code
runs..
I
click the Save button in Excel and then close it myself.

Does anyone know why this is happening???




If
ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule.CountofLines

0 Then
With ActiveWorkbook.VBProject.vbComponents("Sheet1").Co demodule
.DeleteLines 1, .CountofLines
End With
Else
MsgBox prompt:="No Code to Delete!"


End If
Activeworkbook.Save
Activeworkbook.Close true

End Sub

Thanks in advance for your help...
Kimberly

Deleting code in a file with code..
 

Me too. Strange.

Does this happen with every workbook you use, or just one? If you haven't
yet, create a new workbook, put some code in the Sheet1 module and save it.
Run the add-in code on that test workbook and see if you get the same
results.

I can't think of what might cause it to think there are macros that would be
eliminated by a UI save (and not a VBA save.)

Another thing you might try (although very kludgy), is to put the save and
close operations in another macro. Then use OnTime to call that macro 1
second after the main macro runs. Possibly giving it a second to catch up
will do the trick.

If none of that works, you can feel free to email me the add-in and a
workbook on which you know it fails. I can test it on my system and see if
it's add-in, workbook, or other related.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"KimberlyC" wrote in message
...
I'm using Excel 2000