VBA Disable Save and Save As until Certain Requirements are Met

[avd88]

Hello,

I am trying to create a macro that will enable and disable the ability to save a file unless cells: B5, B10, B38 and F38 have something in them.
Any ideas?

Thank you!

 

[mrshl9898]

How's this? (Needs to be placed in "This Workbook" Object)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheets("Sheet2").Range("B5") = "" Or Sheets("Sheet2").Range("B5") = "" Or Sheets("Sheet2").Range("B5") = "" Or Sheets("Sheet2").Range("B5") = "" Then
Cancel = True
MsgBox "Save Cancelled"
Else
Cancel = False
End If


End Sub

 

[Jmoz092]

I use something like this in one of my projects:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
If WorksheetFunction.CountA( _
sheet1.Range("B5,B10,B38,F38")) < 4 Then
Cancel = True        
End If
End Sub

 

[avd88]

Thank you for the reply! I am trying out the first code from mrshl9898, it works but now I am running into the issue where it won't let me save a general blank copy of the document.

 

[avd88]

Saved it on design mode! Problem fixed. Thank you both for your inputs.

 

[Jmoz092]

Two things you could try, one is to exit the sub if you're the user:

If Application.UserName = "[COLOR=#ff0000]YOU[/COLOR]" Then
    Exit Sub
    End If

or use a password to bypass the sub:

Dim pass As String
    pass = InputBox("[COLOR=#ff0000]Enter Admin Password, etc, etc[/COLOR]")
    If pass = "[COLOR=#ff0000]1111[/COLOR]" Then Exit Sub

change the code in red to suit your needs.