Saving Template with VBA requiring fields be filled in.

[Raychill Canuck]

So I'm working in a template that the estimators use. I'm setting it up so that they cannot save it without certain fields being filled in. However, how do I save the template with those fields blank?

 

[mumps]

You could possibly require the user to enter a password in order to save the file. What is the name of the sheet containing the template and which cells in the template do you want to make mandatory?

 

[Raychill Canuck]

The name of the worksheet is "Price Sheet" (the workbook is "Quote Template"). The fields that are mandatory are P19, P29 and P31.

 

[mumps]

Before you start, save your file as a macro-enabled file so that its extension changes to "xlsm". Next place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click ThisWorkbook. Copy/paste the macro into the empty code window that opens up on the right. You will notice that in the code, I used the word "mypassword" as a temporary password. Change this in the code to a password of your choosing (3 occurrences). Close the window to return to your sheet. When you try to save the file, if you use "Save" you will be prompted to use "SaveAs" instead. The macro checks the three mandatory cells to make sure they are populated. If data is missing, you will be asked if you want to save the file with the data missing. If you do, you will be prompted for the password. If you don't save it with the data missing, the cell with the missing data will be highlighted for you to make an entry. After you copy/paste the macro as described above, you should re-save it if you change the password. Give it a try.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim password As String
    password = "mypassword"
    Dim rng As Range
    If SaveAsUI = False Then
        MsgBox "Please save the file using SaveAS."
        Cancel = True
        Exit Sub
    End If
    If WorksheetFunction.CountA(Range("P19,P29,P31")) < 3 Then
        If MsgBox("One or more cells in P19,P29 and P31 is missing data. Do you wish to save with the data missing?", vbYesNo) = vbYes Then
            password = InputBox("Please enter your password to save the file.", "Enter Password")
            If password <> "mypassword" Then
                MsgBox ("Invalid password.  Please try again.")
                Cancel = True
                Exit Sub
            ElseIf password = "mypassword" Then
                '
            End If
        Else
            For Each rng In Range("P19,P29,P31")
                If rng = "" Then
                    MsgBox ("Before saving, you must enter a value in cell " & rng.Address(0, 0) & ".")
                    rng.Select
                    Cancel = True
                    Exit Sub
                End If
            Next rng
        End If
    End If
End Sub

 

[Raychill Canuck]

mumps,

Thanks for your help on this. Month End hit and I haven't had a chance to test it. I see the direction you went with this and I've been weighing the pros and cons of changing my approach.

 

[mumps]

OK. Let me know how it works out.

 

[Raychill Canuck]

Once again, Mumps, thank you. I've had a chance to test this and your code works. I thank you for your time and effort.

 

[mumps]

You are very welcome.