S
suzetter
I am at my wits end with this whole issue
I have tried and tried and tried and tried and tried and I don't know
what next to do
I have tried posting this problem in several forums and I have gotten
help from one person so far who tried my excel workbook on their
machine and it worked (but it's not working on mine)
I have a simple excel 2000 workbook, which has a macro
The macro is a simple macro and for particular reasons, I need to
disable the macro pop-up alert and the "do you want to save the
changes..." alert (In fact I need to disable all the alerts)
I thought this would be a simple exercise and I researched it on the
Net in several forums etc. and so I punched in my code and proceeded
Unfortunately, the code never worked and I e-mailed it to someone who
tried it on their computer and it worked
I tested it on several other computer in the office with Office 2000,
XP, 2003 etc. and it doesn't work either. I still kept getting the
macro and save alerts
So then I thought maybe something is wrong with the workbook itself
So I opened a new BLANK workbook and created a simple macro to disable
the alerts. I would then open it to test, but it didn't because when I
opened it I still got the macro alert and if I typed something in a
cell and attempted to close it without saving, I was still prompted
with the save alert
I stepped through the code, line by line and used watches to see if the
myExcel.DisplayAlerts value was actually changing and it was
But I realised something strange...my code was actually opening another
instance of Excel
You see, the line myexcel.Visible = True opens another instance of
Excel which I didn't realise before
So I continued stepping through the lines until the
myexcel.DisplayAlerts = False was executed to make certain that the
value was changed (and it was)
But before continuing stepping through, I switched to the other
instance of Excel that opened and low and behold if I opened a new
workbook and entered stuff in some cells and tried closing it without
first saving - I didn't get any save alerts.
But unfortunately, if I tried to open a workbook containg a macro, I
still got the macro alert
So, the problem boils down to this:
(1) It would appear that my code is generally right, but it is not
applying to the active instance of excel when I open my workbook
(2) Setting the displayalerts to false seems to be working for the save
alerts but not for the macro alerts.
I know I can certify (attach a digital signature to the file) the macro
and placed it in the trusted sources list, but I really don't want to
do that. And of course I need to know what I'm doing wrong in the code
which is leading to the other instance of Excel opening and apparently
working for at least the save alerts
Here is the exact code I used below and I attached the excel file with
the code that is not working on my machine:
Private Sub Workbook_Open()
Dim myexcel As Object
Dim myworkbook As Object
Set myexcel = CreateObject("Excel.Application")
Set myworkbook = myexcel.ActiveWorkbook
myexcel.Visible = True
myexcel.DisplayAlerts = False
myexcel.ScreenUpdating = False
myworkbook.Save
End Sub
I tried putting this code both in the Sub Workbook_Open routine and the
Sub Auto_Open rountine. Nothing works.
Anyone who can help me please, please, please, please, please feel free
to do so
P.S.
I think I've figured out the other instance of Excel thingy
I changed the code so that myexcel points to the active instance of
Excel (see code below):
Private Sub Workbook_Open()
Dim myexcel As Object
Dim myworkbook As Object
Set myexcel = GetObject(, "Excel.Application")
Set myworkbook = myexcel.ActiveWorkbook
myexcel.Visible = True
myexcel.DisplayAlerts = False
myexcel.ScreenUpdating = False
myworkbook.Save
End Sub
But, it still doesn't work
In fact I'm even more confused
You see, I thought that would have fixed it...but it didn't
I stepped through the code to make certain the myexcel.displayalerts
value did change to false and it did
But when I attempted to open a blank workbook, edited it, and tried
closing it without saving....the bloody save alert came up
SOMEONE PLEASE HELP ME
Attachment filename: excelmacrotest.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=476595
I have tried and tried and tried and tried and tried and I don't know
what next to do
I have tried posting this problem in several forums and I have gotten
help from one person so far who tried my excel workbook on their
machine and it worked (but it's not working on mine)
I have a simple excel 2000 workbook, which has a macro
The macro is a simple macro and for particular reasons, I need to
disable the macro pop-up alert and the "do you want to save the
changes..." alert (In fact I need to disable all the alerts)
I thought this would be a simple exercise and I researched it on the
Net in several forums etc. and so I punched in my code and proceeded
Unfortunately, the code never worked and I e-mailed it to someone who
tried it on their computer and it worked
I tested it on several other computer in the office with Office 2000,
XP, 2003 etc. and it doesn't work either. I still kept getting the
macro and save alerts
So then I thought maybe something is wrong with the workbook itself
So I opened a new BLANK workbook and created a simple macro to disable
the alerts. I would then open it to test, but it didn't because when I
opened it I still got the macro alert and if I typed something in a
cell and attempted to close it without saving, I was still prompted
with the save alert
I stepped through the code, line by line and used watches to see if the
myExcel.DisplayAlerts value was actually changing and it was
But I realised something strange...my code was actually opening another
instance of Excel
You see, the line myexcel.Visible = True opens another instance of
Excel which I didn't realise before
So I continued stepping through the lines until the
myexcel.DisplayAlerts = False was executed to make certain that the
value was changed (and it was)
But before continuing stepping through, I switched to the other
instance of Excel that opened and low and behold if I opened a new
workbook and entered stuff in some cells and tried closing it without
first saving - I didn't get any save alerts.
But unfortunately, if I tried to open a workbook containg a macro, I
still got the macro alert
So, the problem boils down to this:
(1) It would appear that my code is generally right, but it is not
applying to the active instance of excel when I open my workbook
(2) Setting the displayalerts to false seems to be working for the save
alerts but not for the macro alerts.
I know I can certify (attach a digital signature to the file) the macro
and placed it in the trusted sources list, but I really don't want to
do that. And of course I need to know what I'm doing wrong in the code
which is leading to the other instance of Excel opening and apparently
working for at least the save alerts
Here is the exact code I used below and I attached the excel file with
the code that is not working on my machine:
Private Sub Workbook_Open()
Dim myexcel As Object
Dim myworkbook As Object
Set myexcel = CreateObject("Excel.Application")
Set myworkbook = myexcel.ActiveWorkbook
myexcel.Visible = True
myexcel.DisplayAlerts = False
myexcel.ScreenUpdating = False
myworkbook.Save
End Sub
I tried putting this code both in the Sub Workbook_Open routine and the
Sub Auto_Open rountine. Nothing works.
Anyone who can help me please, please, please, please, please feel free
to do so
P.S.
I think I've figured out the other instance of Excel thingy
I changed the code so that myexcel points to the active instance of
Excel (see code below):
Private Sub Workbook_Open()
Dim myexcel As Object
Dim myworkbook As Object
Set myexcel = GetObject(, "Excel.Application")
Set myworkbook = myexcel.ActiveWorkbook
myexcel.Visible = True
myexcel.DisplayAlerts = False
myexcel.ScreenUpdating = False
myworkbook.Save
End Sub
But, it still doesn't work
In fact I'm even more confused
You see, I thought that would have fixed it...but it didn't
I stepped through the code to make certain the myexcel.displayalerts
value did change to false and it did
But when I attempted to open a blank workbook, edited it, and tried
closing it without saving....the bloody save alert came up
SOMEONE PLEASE HELP ME
Attachment filename: excelmacrotest.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=476595