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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,971
Messages
5,508,473
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top