Multiple If Statement

[Daviv]

I want to write a sub to do the following when a user closes the worksheet:

1) Using a message box, check if data has been entered into cell named
"Date". If the user answers yes, save the file. If the user answers no,
goto the "Date" cell.
2) Using a message box, check if data has been entered into cell named
"Shift". If the user answers yes, save the file. If the user answers no,
goto the "Shift" cell.

The code just check for the "Date" Cell but not for the "Shift" Cell.
Actually, there are 8 other cells in my worksheet that I want to check.
Please 1) check my code below, and 2) offer any suggestions on how to write a
better code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ans As Variant

If Worksheets("01-01-07").Range("Date").Value = " " Then
Dans = MsgBox("Have a date been entered? If not, please enter a
date.", vbYesNo)
If Dans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Date")
Cancel = True
ElseIf Worksheets("01-01-07").Range("Shift").Value = " " Then
Sans = MsgBox("Have a work shift been entered? If not, please enter
a work shift.", vbYesNo)
If Sans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Shift")
Cancel = True
Else
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = True
End If
End If
End If
End Sub

 

[Bob Phillips]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Variant

With Worksheets("01-01-07")

If .Range("Date").Value = "" Then
Dans = MsgBox("Have a date been entered? " & _
"If not, please enter adate.", vbYesNo)
If Dans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Date")
Cancel = True
Exit Sub
End If

End If

If Worksheets("01-01-07").Range("Shift").Value = " " Then
Sans = MsgBox("Have a work shift been entered? " & _
"If not, please enter a work shift.", vbYesNo)
If Sans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Shift")
Cancel = True
Exit Sub
End If

End If

Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = True

End With

End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

Hmm... it would seem better to me to check Date and Shift directly,
rather than asking the user (especially if they can't check to see if
they have or not). This is one way do that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const csTITLE As String = "Validation Check"
Dim vAnswer As Variant
With Worksheets("01-01-07")
With .Range("Date")
If Not IsDate(.Value) Then
Do
vAnswer = Application.InputBox( _
Prompt:="Error: Enter Date:", _
Title:="Validation Check", _
Default:=Date)
If vAnswer = Cancel Then 'user cancelled
Cancel = True
Exit Sub
End If
Loop Until IsDate(vAnswer)
.Value = vAnswer
End If
End With
With .Range("Shift")
If Len(Trim(.Value)) = 0 Then
Do
vAnswer = Application.InputBox( _
Prompt:="Error: Enter Shift", _
Title:=csTITLE, _
Default:=1)
If vAnswer = False Then 'user cancelled
Cancel = True
Exit Sub
End If
Loop Until vAnswer <> vbNullString
End If
.Value = vAnswer
End With
End With
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

 

[JE McGimpsey]

note that this will only prompt for the "Shift" cell if it contains
exactly one space character. An alternative that would prompt if it was
actually empty (or had multiple spaces) would be

If Trim(Worksheets("01-01-07").Range("Shift").Value) = "" Then