I need help with a Run-time Error 1004 Please!

[beneduci55]

Hi all,
I have a lot of experience using the Excel UI, but just recently started dabbling in VBA. I created a recipe template for a chef who wanted a way to "SaveAs" the file with the recipe name without having to type it out every time. Using VBA, I set it to save as FileName = a cell on a hidden sheet which combined the "Recipe Title," "Prep," and today's date as mm.dd.yyyy. The formula looks like this:

=Recipe!C1 & " " & "Prep" & " " & MONTH(Recipe!C42) & "." & DAY(Recipe!C42) & "." & YEAR(Recipe!C42)

I created a FormControl Button to execute the Sub SaveAsTitleDate()
Code follows:

Sub SaveAsTitleDate()

Dim Filename As String
Dim FilePath As String
Dim wsHidden As Worksheet

Set wsHidden = Worksheets("Hidden")
FilePath = ThisWorkbook.Path
Filename = wsHidden.Range("C2")

ActiveWorkbook.SaveAs Filename:=Filename & ".xlsm"

End Sub

This worked great! If I had filled out the title as "Carrot Soup," and clicked the button, it would save as "Carrot Soup Prep 01.01.2020.xlsm." Then, the problems arose. Once I have my "Carrot Soup Prep 01.01.2020.xlsm" file, and I make a change, I am unable to save it again via the button! It triggers a Run-time error '1004.' Cannot access read-only document. Through some online research, I found some codes online that were supposed to resolve this issue, but none of them worked. If you could better advise me, I would really appreciate it.

Thanks and Happy New Year!

 

[Kenneth Hobson]

Maybe

If ThisWorkbook.FullName = Filename & ".xlsm" Then
  ActiveWorkbook.Save
  Else
  ActiveWorkbook.SaveAs Filename:=Filename & ".xlsm"
EndIf

 

[beneduci55]

Maybe

If ThisWorkbook.FullName = Filename & ".xlsm" Then
  ActiveWorkbook.Save
  Else
  ActiveWorkbook.SaveAs Filename:=Filename & ".xlsm"
EndIf

Thank you Kenneth, but this results in the same Run-time Error 1004.

 

[Kenneth Hobson]

Before those lines, put this:

Debug.Print Filename & ".xlsm"

After the run, view the result in the Immediate Window. If you do not have that window setup in VBE's menu View select it. I suspect that filename is nothing or not a valid drive:\path\filename.

 

[beneduci55]

Before those lines, put this:

Debug.Print Filename & ".xlsm"

After the run, view the result in the Immediate Window. If you do not have that window setup in VBE's menu View select it. I suspect that filename is nothing or not a valid drive:\path\filename.

Now I have:
Sub SaveAsTitleDate()


Dim Filename As String
Dim FilePath As String
Dim wsHidden As Worksheet


Set wsHidden = Worksheets("Hidden")
FilePath = ThisWorkbook.Path
Filename = wsHidden.Range("C2")


'suggestion 2
Debug.Print Filename & ".xlsm"


'suggestion 1
If ThisWorkbook.FullName = Filename & ".xlsm" Then
ActiveWorkbook.Save
Else
ActiveWorkbook.SaveAs Filename:=Filename & ".xlsm"
End If


End Sub

This still triggers the 1004 error, and the immediate window reads:

VBA Test 4 Prep 1.3.2020.xlsm

which is exactly what it should be (I entered "VBA Test 4" as the recipe title).

Or should it say, for example, C:\Documents\VBA Tests\VBA Test 4 Prep 1.3.2020.xlsm. ? I liked not specifying the whole thing before because it saved as to the same folder in which the user(s) had the blank template, so multiple users could use it on their own computers. Must I do this?

 

[Kenneth Hobson]

I think that I would do:

Sub SaveAsTitleDate()  
    Dim Filename As String, FilePath As String, wsHidden As Worksheet
    
    Set wsHidden = Worksheets("Hidden")
    FilePath = ThisWorkbook.Path & "\"
    Filename = wsHidden.Range("C2") & ".xlsm"

    If ThisWorkbook.FullName = FilePath & "\" & Filename Then
      ActiveWorkbook.Save ThisWorkbook.FullName
      Else
      ActiveWorkbook.SaveAs Filename:=FilePath & "\" & Filename
    End If
End Sub

I don't see the need for wsHidden. There are times one must make the sheet visible, do some things, and then rehide it. Doing some things in a hidden sheet without unhiding would like cause an error.

 

[beneduci55]

I think that I would do:

Sub SaveAsTitleDate()  
    Dim Filename As String, FilePath As String, wsHidden As Worksheet
    
    Set wsHidden = Worksheets("Hidden")
    FilePath = ThisWorkbook.Path & "\"
    Filename = wsHidden.Range("C2") & ".xlsm"

    If ThisWorkbook.FullName = FilePath & "\" & Filename Then
      ActiveWorkbook.Save ThisWorkbook.FullName
      Else
      ActiveWorkbook.SaveAs Filename:=FilePath & "\" & Filename
    End If
End Sub

I don't see the need for wsHidden. There are times one must make the sheet visible, do some things, and then rehide it. Doing some things in a hidden sheet without unhiding would like cause an error.

I'm still getting the same runtime error, but I discovered something...I have a Mac, as does the chef I created the spreadsheet for. When I opened it at work on a PC, I had no issues. Is there some Mac-specific code I need to use???

 

[Pecoflyer]

@Beneduc
Please do not quote entire posts unnecessarily. Use "Quick reply" instead
Please wrap your code with code tags ( the #button).
Thanks