Require sheets to open if yes

[pywhacket]

Hello,

I have a multiple sheet workbook that I am using for requisitions. On the first sheet I have a choice of shipping required. If yes then all of the other tabs need to be filled out. How do I force this to occur? I would like to make this happen and also make it so that the workbook will not save or not close if the forms are not filled out. Thanks in advance for the help.

Michele

 

[pbornemeier]

You would have BeforeClose and BeforeSave events in the ThisWorkbook code page call a function that verifies that the cells on the appropriate pages are filled in before allowing the Close or Save functions to continue.

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Place this subroutine in the ThisWorkbook code page
    Application.EnableEvents = False
    If CellsFilledIn = False Then Cancel = True
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Place this subroutine in the ThisWorkbook code page
    If CellsFilledIn = False Then Cancel = True
End Sub
 
Function CellsFilledIn()
    'Place this function in the ThisWorkbook code page
 
    Dim booFilledIn As Boolean
    Application.EnableEvents = False
    On Error GoTo ErrorHandler
 
    booFilledIn = True
    If Worksheets("Sheet1").Range("G23") = "Yes" Then 'Shipping Required
        If Worksheets("Sheet2").Range("A1").Value = "" Then booFilledIn = False
        If Worksheets("Sheet2").Range("A5").Value = "" Then booFilledIn = False
        If Worksheets("Sheet2").Range("B7").Value = "" Then booFilledIn = False
        If Worksheets("Sheet2").Range("B9").Value = "" Then booFilledIn = False
        If Worksheets("Sheet3").Range("C1").Value = "" Then booFilledIn = False
        If Worksheets("Sheet3").Range("C2").Value = "" Then booFilledIn = False
        If Worksheets("Sheet3").Range("C3").Value = "" Then booFilledIn = False
        If Worksheets("Sheet3").Range("F6").Value = "" Then booFilledIn = False
    End If
    If booFilledIn = False Then
        MsgBox "Shipping is selected.  Fill in appropriate cells" & vbCrLf & _
            "or remove 'Yes' from Sheet1 Cell G23."
    End If
    CellsFilledIn = booFilledIn
    GoTo End_Function
 
ErrorHandler:
    MsgBox "Error " & Err.Number & " in CellsFilleIn function: " _
        & Err.Description
 
End_Function:
    Application.EnableEvents = True
 
End Function

Modify the 2 references to cell G23 to point to the shipping required choice. Modify the tests in the function to check the appropriate cells. The tests I am showing only check to see if the cells are blank, you could also test for required values instead of just not blank.