Run time error 1004 - File read only

[Chaviar]

Hello,

I've cobbled together a macro to save a file with the contents of a cell number. This works nicely the first time it is created unfortunately when it is reopened and edited I am unable to resave the workbook as I get the Run-time error 1004 saying that the file is read only and cannot be saved.

What I am aiming for is to calculate the next number in a document list using a lookup which is working - then passing this number to a cell. Upon save the macro copies and repastes the value into the cell where there was a lookup formula to keep that document number. The macro then passes the value in cell C2 to be the recommended name in a save as. This works fine despite my poor use of VB (I am a beginner).

The error message comes up when I reopen the workbook edit it and then resave without changing the cell c2 which I dont want to do.

It is saved to a document library on sharepoint online and I have been through this to check that permissions are not set incorrectly and I suspect the casue lies with the macro I've set up below particularly the save as line.

It doesn't need to be save as once it is being edited so I had thought to put an if exists check in there and then automatically save it but before I do that I wondered if anyone can give me some help removing the 1004 error?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Totals")
 Set rng6 = .Range("C2:C7") '.End(xlDown).Offset(0, 6)
 rng6.Copy
 Worksheets("Totals").Range("C2:C7").PasteSpecial xlPasteValues
End With
 Application.CutCopyMode = False    
 Application.EnableEvents = False
    rtn = Application.Dialogs(xlDialogSaveAs).Show(arg1:=ThisWorkbook.Sheets("Totals").Range("C2").Value)
    Application.EnableEvents = True
    If Not rtn Then Cancel = True
End Sub

Thanks if anyone can help
C

 

[Jerry Sullivan]

Hi Chaviar and Welcome to the Board,

I wasn't able to replicate the Error 1004 you received; however I think the code that you posted will try to save the file twice if the user doesn't Cancel.

The code will try to save the workbook when the user clicks the "Save" button in the SaveAs Dialog, then it will try to save the workbook again when the Workbook_BeforeSave procedure has ended.

It's possible that this double-save resulted in your "Read-only" errors when you tried to save to a Sharepoint site.

Try this modified version....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sDefaultFileName As String

        
    Cancel = True

    
    With Worksheets("Totals").Range("C2:C7")
        .Value = .Value
        sDefaultFileName = .Cells(1).Text
    End With

    
    On Error GoTo CleanUp

    
    With Application
        .EnableEvents = False
        .Dialogs(xlDialogSaveAs).Show arg1:=sDefaultFileName
    End With

    
CleanUp:
    Application.EnableEvents = True
End Sub

If that doesn't work, I'd suggest you test the same code saving the file to your local computer, then a Network folder that isn't on a Sharepoint server.

If those two tests work, there's a good chance the problem is related to saving to a Sharepoint evironment.