Must fill in a cell before being able to save

[SZ11OT9]

I found this topic, but i would like to do it a bit different.
http://www.mrexcel.com/forum/showthread.php?t=75707

I am creating a form for others to fill in. But they don't fill it completely.
So i want to force them to

What they need to do:

If they fill in Cell G31 or H31 they must also fill in cel F31, before it will save the document.

If they try to save the file without F31 filled, it should give a messagebox that
says: Fill in Volume.

I've got a bit, but it won't work...

Privatesub Workbook_BeforeSave(ByVal SaveAsUi  As Boolean, Cancel As Boolean) 
 
   Dim msg As String 
 
   If Not Worksheets("Sheet1"). Range("G31").Value Like "##" then 
        msg = "Befor saving, fill in Volume." 
        Cancel = True
    End If
 
   If Not Worksheets("Sheet1").Range("H31").Value Like "##" Then 
        msg = msg & vbCrLf & "Before saving, fill in Volume." 
        Cancel = True
    End If
 
   If Cancel Then
        MsgBox msg 
    End If
 
 End Sub

 

[Sandeep Warrier]

Hi,

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (Sheets("Sheet1").Range("G31").Value <> "" Or Sheets("Sheet1").Range("H31").Value <> "") And Sheets("Sheet1").Range("F31").Value = "" Then
        MsgBox "Before saving, fill in Volume."
        Cancel = True
    End If
End Sub

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[jim may]

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
   Dim msg As String
    If Worksheets("Sheet1").Range("G31").Value <> "" And _
        Worksheets("Sheet1").Range("F31").Value = "" Then
        msg = "Before saving, fill in Volume."
        Cancel = True
    End If
    If Worksheets("Sheet1").Range("H31").Value <> "" And _
        Worksheets("Sheet1").Range("F31").Value = "" Then
        msg = "Before saving, fill in Volume."
        Cancel = True
    End If
 
   If Cancel Then
        MsgBox msg
    End If
 End Sub

 

[SZ11OT9]

Nope, it just saves the file even when the cells are empty.

 

[jim may]

Perhaps your Events have been disabled.

Go to the Immediate Window (Ctrl+G)in the VBE and enter

Application.enableevents = True (And Press Enter)

 

[SZ11OT9]

Nope, it still doesen't work.

Is it because i use Excel 2007?
the file is saved as Macro Enabled excel file.

Do i need to enter the formula with the designer mode on?

 

[SZ11OT9]

Nope, it still doesen't work.

Is it because i use Excel 2007
the file is saved as Macro Enabled excel file.

Do i need to enter the formula with the designer mode on?

Other comment. The cells are usually protected with a password (except F31, G31 and H31)

And the "Base file" is saved as Read Only.

 

[jim may]

Try this............

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
   Dim msg As String
    If Worksheets("Sheet1").Range("G31").Value <> "" And _
        Worksheets("Sheet1").Range("F31").Value = "" Then
        msg = "Before saving, fill in Volume."
        Cancel = True
    End If
    If Worksheets("Sheet1").Range("H31").Value <> "" And _
        Worksheets("Sheet1").Range("F31").Value = "" Then
        msg = "Before saving, fill in Volume."
        Cancel = True
    End If
    
    If Worksheets("Sheet1").Range("G31").Value = "" Or _
        Worksheets("Sheet1").Range("H31").Value = "" Then
        msg = "Before saving, fill in Volume."
        Cancel = True
    End If
 
   If Cancel Then
        MsgBox msg
    End If
 End Sub

 

[SZ11OT9]

I hate to post this, but it still won't run. I can save it without filling in cells G31 or H31.

What am i doing wrong?

I've tried saving it in a new module, in the sheet it'sself... but nothing will do the trick...

 

[Norie]

The code is a workbook event so therefore should, wait for it, go in a workbook module.

Specifically the ThisWorkbook module.