Need a save and close macro that does not care if the "edit workbook" button has be clcked

[rt3]

I am new to VBA and macros and have learned a lot searching this forum but I have a problem that I cannot find the answer to in previous threads. I will explain what I am trying to do, what I know how to do, then what I don't know how to do. This way if there is a better solution you can suggest one.

I have multiple excel 2010 spreadsheets loaded in one directory on our departments SharePoint. There are various users that access these files, they enter data save it and exit. The files open up as server read only, so they have the big yellow bar across the top that says "This workbook was opened from a server in read-only mode" and an "edit workbook" button that you can push and then click the save button to save the file without being prompted where to save it with a "save as" dialog box. My problem is that some users do not click the edit button and are then prompted with a save as box when click save,then they click the wrong file name and overwrite another workbook from the directory.

I want to create a button on the sheet that launches a macro that saves and closes the workbook. I have found this code here and have it working. The problem is the edit workbook button. some users click it and some do not. if I use a ActiveWorkbook.LockServerFile command, the macro will push the button for me but if the button has been clicked by the user, the script fails. I don't know how to write an if then to fix this

[1nf1n1ty]

Hi,

You can try something like this
Add this to This Workbook section

Please Login or Register  to view this content.

And this to your module section:

Please Login or Register  to view this content.

[rt3]

Thanks I will give it a try, if you have the time, Can you tell me how it works as well?

[1nf1n1ty]

Please Login or Register  to view this content.

[rt3]

Thank you for the explanation of the code. I don't think i explained my problem well enough. The code you gave me helps if the users click my macro button or want use another way to save. The problem I am having is that some users click the excel button "edit workbook" on the yellow server read only bar or and some do not. Those that click the the "edit workbook" button don't get prompted with the save as box , those that don't get the save as box. My users are mostly unsophisticated with MS office and probably don't know what ctrl s does. I might be able to find a solution using the tools you have explained but if you have one, feel free to post it. Thanks again

[rt3]

2832165.png

This is the button some click and some dont click

[rt3]

It looks like I need a way for my macro to detect if the file is in server read only mode, then I can use the ActiveWorkbook.LockServerFile to switch to edit mode if necessary, any thoughts

This works when the "edit workbook button has not been pushed

Sub savebutton()

ActiveWorkbook.LockServerFile


ActiveWorkbook.save
ActiveWorkbook.Close
End Sub

and i get a 1004 runtime error when it has been pushed

[rt3]

Found the solution,have the code read the application title and act accordingly

Sub savebutton()

If InStr(1, Application.Caption, "Read-Only") > 1 Then
ActiveWorkbook.LockServerFile
ActiveWorkbook.save
ActiveWorkbook.Close
Else
ActiveWorkbook.save
ActiveWorkbook.Close

End If
End Sub