Opening workbook with 0 value should be other value before able to save

[NickMulders]

Hello guys,

I've been trying to combine some codes to make a mandatory cell input before one can save the workbook. the thing is some colleagues have to input values into a cell which is normally blank or 0 before they can close the workbook as this is often forgotten.. The workbook contains multiple tabs and i need the mandatory cell input to be in a workbook that is not opened by default. So lets say the workbook opens on sheet1 but the mandatory input has to be in sheet2 cell C11 before they can close the workbook. I had one instance where I got the code to work but it would prevent me from saving because the cell was blank/zero. I need it to open the workbook clear cell C11 of sheet2 and then have a mandatory input in that cell before it can be saved (with a msgbox on error) I am a VBA newbie and i've tried different codes found on the internet but when one seemed to work the other one cancelled it out... Any help would be greatly appreciated.

Nick

 

[NickMulders]

Hey guys I got it to work to the point where it clear the cell and it has to be filled in, which is a great leap ahead! Now I've run into a new problem the workbook is a blank "invoice" format which employees fill in which automatically generates an invoice, but now when I open the workbook it clears the cell which I need the data from to make the final invoice. We normally change the name of the workbook when it's saved, is there any code that prevents it from clearing the cell when the name is other dan let's say "Default workbook" so it clears the cell when the blank is opened but when it's renamed to "workbook1" it is unchanged. Again many thanks

 

[NickMulders]

Again I apologise for not adding the code to my post which could probably benefit you guys greatly...

Private Sub Workbook_Open()    Application.EnableEvents = False
    With ThisWorkbook
        With .Worksheets("Containers tellijst")
            .Range("C11").ClearContents
    End With
    Application.EnableEvents = True
End With
End Sub




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.Sheets("Containers tellijst").Range("C11").Value = "" Then
Cancel = True
MsgBox "Niet alle velden zijn ingevuld"
End If
End Sub