Results 1 to 2 of 2

Thread: What is wrong with my Code

  1. #1

    What is wrong with my Code



    Register for a FREE account, and/
    or Log in to avoid these ads!

    HI Guys!

    i would really appreciate some help. Basically, I have written this VBA code.

    What it should do is: On close check sheets 2 - 30, if O8 has data and O4 doesnt have data, then prompt the user to enter required field.

    For some reason i cant get it to work!

    Code below:


    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call CheckField
    Cancel = True
    End Sub
    Sub CheckField()
    Application.ScreenUpdating = False
    For Each Worksheet In ActiveWorkbook.Worksheets
    If Worksheet.Range("O8").Value <> Not Empty And Worksheet.Range("O4").Value <> Empty Then
    MsgBox "Please Fill in Required Fields Fields:" _
    , vbOKOnly, "Missing Information Required"
    Cancel = True
    End If
    Application.EnableEvents = False
    Next
    End Sub

  2. #2
    Try this

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)    Cancel = CheckField
    End Sub
    
    
    Function CheckField() As Boolean
    Dim ws As Worksheet
    
    
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        For Each ws In ActiveWorkbook.Worksheets
        
            If ws.Range("O8").Value = "" And ws.Range("O4").Value = "" Then
        
                MsgBox "Please Fill in Required Fields Fields on sheet " & ws.Name & ":", _
                       vbOKOnly, _
                       "Missing Information Required"
                CheckField = True
                Exit For
            End If
        Next
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •