Still save-dialog prompt after displayalerts set to false

stef.ong

New Member
Joined
Oct 2, 2006
Messages
12
This is some code I used in a macro :

Application.DisplayAlerts = False
ActiveWindow.Close SaveChanges:=False
Application.DisplayAlerts = True

On some PC it is closing the file without prompting a dialog to save or not save the file. However on another PC it still prompt for this info ???
Same Excel 2000 SP3 is used on all 4 PC

Does anyone has a clue ??
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Not really, but why not investigate the beforesave event of the workbook object just in case that event is not disabled by your aforementioned code?

From the helpfiles:

Syntax

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)

SaveAsUi True if the Save As dialog box will be displayed.

Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I was also suggest you check the macro security on the systems it's not working on. They might be set to HIGH, which would stop your code from running at all.

HTH
Cal
 

stef.ong

New Member
Joined
Oct 2, 2006
Messages
12
Thanks for the replies so far.

patrickmuldoon99, I was playing around with the beforesave/beforeclose events but I can't get it working (I think). Do you have an example since this is farmost the easiest way of learning ...

Cbrine, the security is on all PC's the same : medium. I did change this to high but indeed the macro is not running at all. So this is not useful.

Stef
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345

ADVERTISEMENT

something like...

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)

cancel = true

end sub
 

stef.ong

New Member
Joined
Oct 2, 2006
Messages
12
patrickmuldoon99,

OK now I found out how it can be used. But as far as I can see it is only working for its own workbook. What I'm trying to so is closing another previously opened workbook. The file in which the acript is running stays open.
Still no solution for my problem but thanks anyway.

In my opinion it should be something with settings or options or ... because it is on a similar PC with same Excel version and same script in vba.

Stef.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

Stef,
So you have a workbook where you code resides, and you want to close another workbook where the code doesn't exist. Couple of questions.
1. Does your code open this other workbook?
2. If your code doesn't open the workbook, does this other workbook have a standard name?
3. The code you posted should work fine for closing the activewindow workbook, but since we can see your other code, we can't tell what the activewindow workbook is. Can you paste more of the code in question.

Cal
 

stef.ong

New Member
Joined
Oct 2, 2006
Messages
12
Cal,

Below you can find all the code which I think is involved in opening, saving and closing files in the script. A short explanation :

After I opened the .xls with the script, it is started with a command button. This opens a dialog to open another file ( named quote ), this file is used to retrieve some info and save this info in another file derived from the original one ( named psani ). At the end I want to close the quote first without any alerts and then also close the original file to endup with the derived psani-file.

The ... are parts which are irelevant for this problem. Don't forget that this code is working fine on several PC's but one gives the problem !!

script :
...

' Open dialog-box to select the quotefile
quotefile = Application.GetOpenFilename("Qoute file (*.xls), *.xls", 1, "Select qoute file ")
If quotefile = "False" Then
Exit Sub
End If

...

' Add 'psani' in front of the quotefilename and save
psanifilename = "psani " & (quotefilename)
psanifile = Application.GetSaveAsFilename(psanifilename, "Psani file (*.xls), *.xls", 1, "Change Psani Document Name")
If psanifile = "False" Then
Exit Sub
End If
ActiveWorkbook.SaveCopyAs (psanifile)

...

' Open the quote-file and get projectinfo
Application.ScreenUpdating = False
Workbooks.Open (quotefile)
...

' Open the psani-file and fill in the info
Workbooks.Open (psanifile)

...

' Open the quote-file and get configurations
Windows(quotefilename).Activate

...

' Close the quotefile
Application.DisplayAlerts = False
ActiveWindow.Close SaveChanges:=False
Application.DisplayAlerts = True
Windows(psanifilename).Activate

...

' Close original psani.xls and previously saved psani document
Application.DisplayAlerts = False
Windows("psani.xls").Close SaveChanges:=False
Application.DisplayAlerts = True

...
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Stef,
I would suggest you make changes to setup references to the actual workbooks, versus using the activewindow. I believe you may have not included all the code, so you may need to make further revisions. By setting references to your workbooks you can always reference them without worrying about the activewindow.

Code:
Dim wbQoute As Workbook, wbPsani As Workbook
'Open dialog-box to select the quotefile
quotefile = Application.GetOpenFilename("Qoute file (*.xls), *.xls", 1, "Select qoute file ")
If quotefile = "False" Then
Exit Sub
End If

' Add 'psani' in front of the quotefilename and save
psanifilename = "psani " & (quotefilename)
psanifile = Application.GetSaveAsFilename(psanifilename, "Psani file (*.xls), *.xls", 1, "Change Psani Document Name")
If psanifile = "False" Then
Exit Sub
End If
ActiveWorkbook.SaveCopyAs (psanifile)


' Open the quote-file and get projectinfo
Application.ScreenUpdating = False
Set wbQoute = Workbooks.Open(quotefile)

' Open the psani-file and fill in the info
Set wbPsani = Workbooks.Open(psanifile)

' Open the quote-file and get configurations
wbQoute.Activate

' Close the quotefile
Application.DisplayAlerts = False
wbQoute.Close SaveChanges:=False
Application.DisplayAlerts = True
wbPsani.Activate
 

stef.ong

New Member
Joined
Oct 2, 2006
Messages
12
Cal,

I tried your tip -it is a good idea to do so- but to my problem it doesn't help. As I said before I think it has something to do with settings on Excel or even PC (???).

I tried it on 5 different PC's and it all was working well except for that one PC. All PC's are from the same type and have the same SW and SW-versions installed.

Stef.
 

Forum statistics

Threads
1,137,366
Messages
5,681,074
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top