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

 

[SZ11OT9]

That was what i was doing wrong :shame:

It now runs great on this code:

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

Thank you all for your time and efford!!!

 

[mikerickson]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim uiValue As Variant, VolumnCell As Range
    Set VolumnCell = ThisWorkbook.Sheets("Sheet1").Range("F31")
    If CStr(VolumnCell.Offset(0, 1)) & CStr(VolumnCell.Offset(0, 2)) <> vbNullString Then
        If CStr(VolumnCell) = vbNullString Then
            uiValue = Application.InputBox("The Volumn must be entered before saving." & vbCr & vbCr & "Volumn:", Type:=7)
            If uiValue = False Then uiValue = vbNullString
            VolumnCell.Value = uiValue
        End If
        Cancel = (CStr(VolumnCell) = vbNullString)
    End If
End Sub

 

[sadi]

Hello Every one,

Need some help on it, I want to input all cell (Row) Before Column H if user fill any cell in column H, Please look below

[TABLE="width: 299"]
<tbody>[TR]
[TD]

[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

If user input in H1 then have to input all cell before cell H1 (A1:G1)