VBA close without saving on read only file

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
hello people.

I am confused. I have written a macro which asks various questions of users in message boxes, then copies the results sheet to a new workbook, which is saved with a new name.

So far so good.

However, having saved the results, I wish to close the original questionnaire file without saving changes, so it can be used again by another user. I have used the following code, which appears at the end of the macro, after the quiz has been completed.
(quizname is the codename for this set of answers, and use score, used as the new filename. Quizm is the name of the questionnaire file, which should be closed unaltered, with no notifications to the user).

Code:
'...
file = "C:\Documents and Settings\Mark\My Documents\Quiztest\" & quizname & ".xls"

'save the completed quiz
    Sheets("Results").Copy
    ActiveWorkbook.SaveAs Filename:=file, _
    FileFormat:=xlOpenXMLWorkbook, Password:="grifter", WriteResPassword:="grifter", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    MsgBox "File saved as" & quizname & ".xls", vbInformation, "Quiz Completed - Well Done!"
    
'Protect the results sheet
Sheets("Results").Protect Password:="grifter"
ScreenUpdating = False
Workbooks("quizm").Close savechanges = False
ScreenUpdating = True
End Sub

The Questionnaire file, Quizm is read only with a password, although this may not be relevant. For some reason, when I run the macro I get a window popping up offering to save Quizm as "copy of Quizm". It doesn't even ask me if I want to save it, it just appears with the final save as window. How do I stop this happening?

Any advice most welcome - this is the last thing I need to do to get this working smoothly, and I just can't work it out - the Internet has not been any help so far... :¬(

Thanks for reading!
Mark
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Possibly

Rich (BB code):
Application.DisplayAlerts = False
Workbooks("quizm").Close savechanges := False
Application.DisplayAlerts = True
 

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
Ah. Yes. I see.

*Shuffles out of sight without drawing attention to himself*

Thank you. You have saved my hair from a distressing premature extraction event.

You are very kind, and thank you for the help. Maybe time for more coffee, I think.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,257
Messages
5,657,627
Members
418,404
Latest member
Sneijders

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