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

NickMulders

New Member
Joined
Apr 5, 2016
Messages
8
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
Again I apologise for not adding the code to my post which could probably benefit you guys greatly...

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top