Run time error 1004 - File read only

Chaviar

New Member
Joined
Apr 15, 2013
Messages
1
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?

Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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....

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,203,455
Messages
6,055,541
Members
444,794
Latest member
HSAL

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
Back
Top