Help with SaveAs path

[BGrubb]

I have an .xlsm file posted on Sharepoint to be accessed by a group of users some of which are not very proficient in Excel. I have a screen that opens when the file is opened that instructs them to save the file (form) on their PC before using it. Several have saved over it on the Sharepoint site or saved a copy on the sharepoint site since that is what comes up when they use "SaveAs". Is there something I can put in the code so it will come up to show their computer instead of the Sharepoint site to save it to. I tried ChDir to C:\ but that doesn't work because Sharepoint is on their C drive.

Any help would be appreciated.

 

[BGrubb]

AlphaFrog, This is perfect, just what I was looking for.

 

[AlphaFrog]

You're welcome. I'm glad it worked out for you.

 

[BGrubb]

One more thing. They can still cause a problem is they choose "Cancel" on this screen. Any ideas how I can prevent that?

 

[AlphaFrog]

You can never make it 100% bulletproof.

This would annoy (and insult) me as an end user, but you asked for it so here it is...

Sub SaveAs_My_Docs()
    
    Dim Filename As String
    
    ChDrive "C"
    ChDir Environ$("USERPROFILE") & "\My Documents\"
    
    Do
        Filename = Application.GetSaveAsFilename( _
                   FileFilter:="xls Files (*.xls*), *.xls*")
        If Filename <> "False" Then
            ActiveWorkbook.SaveAs
        Else
            MsgBox "You must save a copy." & vbCr & "Yes, Bob. I'm looking at you.", vbCritical, "Save a Copy"
        End If
    Loop Until Filename <> "False"
    
End Sub

 

[BGrubb]

That works for me. Thanks again.

 

[xenou]

Setting the file to open read-only would accomplish a similar goal (this is in options in the save as dialog). Though I also find some users get confused - they think the alert that says the file is read-only means it cannot be opened at all. It would prevent users from saving over the original and would require them to find a new name/directory for the file when saving - but that could still be anywhere. It's not foolproof since the file can be saved with a new name and then re-saved in place of the old file (one would have to *want* to do this though to get this far).