Saving using a Password

[slickp007]

Excel 2003

Hi, i've written some code in the workbook beforesave to make certain fields mandatory as below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cellcontents = Sheets(1).Range("B4").Value
If Cellcontents = "" Then
Cancel = True
MsgBox "Cell B4 is empty - Not Saving!", vbOKOnly, "Check Cells"
Exit Sub
End If

Cellcontents = Sheets(1).Range("D4").Value
If Cellcontents = "" Then
Cancel = True
MsgBox "Cell D4 is empty - Not Saving!", vbOKOnly, "Check Cells"
Exit Sub
End If

Cellcontents = Sheets(1).Range("F4").Value
If Cellcontents = "" Then
Cancel = True
MsgBox "Cell F4 is empty - Not Saving!", vbOKOnly, "Check Cells"
Exit Sub
End If

Cellcontents = Sheets(1).Range("C6").Value
If Cellcontents = "" Then
Cancel = True
MsgBox "Cell C6 is empty - Not Saving!", vbOKOnly, "Check Cells"
Exit Sub
End If

Cellcontents = Sheets(1).Range("E6").Value
If Cellcontents = "" Then
Cancel = True
MsgBox "Cell E6 is empty - Not Saving!", vbOKOnly, "Check Cells"
Exit Sub
End If

End Sub


However a problem i have found is that you can't save this say if you was just administering this workbook. So an idea i had was to have a password cell, so that before save if this cell had a particular string in there then you could save without having to fill in the mandatory fields, and then on open of the workbook this password field would be cleared, however i'm not sure how to do this. Any help?

 

[PHV]

In the debug window (Ctrl-G) type the following before saving the workbook:
Application.EnableEvents=False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Create an Admin procedure and and Prod procedure. Admin will assign a value of FALSE to A1 on sheet Admin, which you can hide. Prod assigns TRUE.

I streamlined your code as well...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim CellContents As Range
    If Sheets("Admin").[A1] Then
        For Each CellContents In Range(Cells(4, "B"), Cells(4, "F"))
            If CellContents.Value = "" Then
                Cancel = True
                MsgBox "Cell " & CellContents.Address & " is empty - Not Saving!", vbOKOnly, "Check Cells"
                Exit Sub
            End If
        Next
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[slickp007]

This code doesn't work, i get the error message:

Run time error 9: Subscript out of range


When i go debug its pointing at Ln 3 Col 1.

Help?

I'm not to sure how to create these Admin and Prod procedures, sorry a bit of a newby to VBA

 

[SkipVought]

This is all it takes...

Sub Admin()

End Sub

Also, your range references ought to refer to a SPECIFIC Sheet Object. What sheet is Range(Cells(4, "B"), Cells(4, "F")) in?

BTW, the code does run without error. I tested it again just now!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[slickp007]

They are in sheet 1

 

[slickp007]

I still get the same runtime error

 

[SkipVought]

Please copy and post all the code and highlight the line that errors.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}