CODEWITHSUNDEEP
excelexcel-2010vba
Gerasimos.Zap
Gerasimos.Zap

Reputation: 159

Deny Save As if cells do not have a value

I have a script that is used to save an excel template based on the contents of two cells/named ranges (FailReportSN and FailReportDD). My issue is that the end users do not always remember to enter values into those two cells before running the save as script below. What I need to do is modify my current script to only save if there are values in both cells.

Sub saveAsFATPMM() 
Dim PathMac As String, Path As String, FolderPath As String

 If Application.PathSeparator = ":" Then 
  FolderPath = "Volumes:Server:Groups:METI:Quality Control:METIman:" 
  PathMac = FolderPath & Sheets("Failure Report").Range("FailReportSN").Text & _ 
      " - FATP - " & Sheets("Failure Report").Range("FailReportDD").Text & ".xlsm" 
   'Format(Date, "mm-dd-yy") 
  ThisWorkbook.SaveAs Filename:=PathMac, FileFormat:=53, CreateBackup:=True 
 Else 
  FolderPath = "\\server\server\Groups\METI\Quality Control\METIman\" 
  Path = FolderPath & Sheets("Failure Report").Range("FailReportSN").Text & _ 
  " - FATP - " & Sheets("Failure Report").Range("FailReportDD").Text & ".xlsm" 
  'Format(Date, "mm-dd-yy") 
  ThisWorkbook.SaveAs Filename:=Path, FileFormat:=52, CreateBackup:=True 
 End If

MsgBox "Your file has been saved. Thank you." 
End Sub

Upvotes: 0

Views: 31

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Use conditional If to check for those values first. In the code below I check to make sure the Len of that Range is not 0 (or False in this case, since 0 equates to False here). I also refactored a bit to get rid of essentially duplicate code.

Sub saveAsFATPMM()

With Sheets("Failure Report")

    If Len(.Range("FailReportSN")) And Len(.Range("FailReportDD")) Then

        Dim PathMac As String, Path As String, FolderPath As String, fFormat as Long

        If Application.PathSeparator = ":" Then

            FolderPath = "Volumes:Server:Groups:METI:Quality Control:METIman:"
            fFormat = 53

        Else

            FolderPath = "\\server\server\Groups\METI\Quality Control\METIman\"
            fFormat = 52     

        End If

        Path = FolderPath & .Range("FailReportSN").Text & _
            " - FATP - " & .Range("FailReportDD").Text & ".xlsm"

        ThisWorkbook.SaveAs Filename:=Path, FileFormat:=fFormat, CreateBackup:=True

        MsgBox "Your file has been saved. Thank you."

    Else

        MsgBox "File not saved! Enter Fail Report Values and Try Again!"

    End If

End With

End Sub

Upvotes: 1

Related Questions