| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple If Statement
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 -- Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple If Statement
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) "Daviv" wrote in message ... 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 -- Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple If Statement
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 In article , Daviv wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple If Statement
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 In article , "Bob Phillips" wrote: 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) "Daviv" wrote in message ... 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 -- Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple IF statement | Excel Worksheet Functions | |||
| I think I need a multiple IF statement, or is there another way? | Excel Worksheet Functions | |||
| multiple if statement | Excel Discussion (Misc queries) | |||
| If statement with multiple criteria and multiple results | Excel Discussion (Misc queries) | |||
| Multiple If Statement | Excel Worksheet Functions | |||
Linear Mode
