Can't get VBA code to read every sheet individually... Please help, I'm sure I've missed something silly

C

Chassee

New Member
Joined
Mar 21, 2023
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi There.... I am creating a code to prevent a user from doing "Save As" if not all boxes I designate are filled in based on other parameters on that excel sheet (such as certain cells having values). I have 35 different sheets that are all the same (it's for different devices being tested by our techs, we don't necessarily use every sheet every time) and one sheet that is different. I want excel to perform the code below (plus 22 other if's) on each page if the if and and statement is met.

This code doesn't seem to work though but no issues when compiling the code. Any help would be great. Below is my code:


Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "BF" Then
End If
If Cells(17, 5) = "Reduced Pressure" And Cells(29, 5) = "" Then
MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto Cells(29, 5)
End If

If ws.Name Like "BF" Then
End If
If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto Cells(31, 5)
End If
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
A couple of things here. First, this code will do nothing:
VBA Code: 
If ws.Name Like "BF" Then
End If

Second, you should have a 'next' command which typically accompanies a For command and tells it to loop back.
 
Upvote 0
Candyman8019 said:
A couple of things here. First, this code will do nothing:
VBA Code: 
If ws.Name Like "BF" Then
End If

Second, you should have a 'next' command which typically accompanies a For command and tells it to loop back.
Click to expand...
Thank you. I will update and a next, how can I make it read each sheet individually where the sheet name contain the letters "BF"
 
Upvote 0
You'll want to put something inside the If statement, for example:

VBA Code: 
If ws.Name Like "BF" Then
   
    If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
        MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
        Cancel = True
        Application.Goto Cells(31, 5)
    end if
End If
 
Upvote 0
Candyman8019 said:
You'll want to put something inside the If statement, for example:

VBA Code: 
If ws.Name Like "BF" Then
  
    If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
        MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
        Cancel = True
        Application.Goto Cells(31, 5)
    end if
End If
Click to expand...
I have updated the code to have this now. The error I receive is "Can't have next without for"

VBA Code: 
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "BF" Then
       If Cells(17, 5) = "Reduced Pressure" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
        End If
         
       If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
         
        If Cells(17, 5) = "Reduced Pressure" And Cells(33, 5) = "" Then
          MsgBox "RELIEF VALVE missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(33, 5)
         End If
         
        If Cells(17, 5) = "Double Check" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
         
        If Cells(17, 5) = "Double Check" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
         
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
         
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(37, 5) = "" Then
          MsgBox "AIR INLET missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(37, 5)
         End If
         
        If Cells(17, 5).Value > "" And Cells(39, 6) = "" Then
          MsgBox "SHUT OFF VAVLE #1 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 6)
         End If
         
        If Cells(17, 5).Value > "" And Cells(39, 14) = "" Then
          MsgBox "SHUT OFF VAVLE #2 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 14)
         End If
         
        If Cells(29, 5).Value > "" And Cells(19, 5) = "" Then
          MsgBox "ASSEMBLY TEST FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(21, 5) = "" Then
          MsgBox "MANUFACTURER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(22, 5) = "" Then
          MsgBox "SIZE FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(23, 5) = "" Then
          MsgBox "LOCATION missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(17, 12) = "" Then
          MsgBox "ASSEMBLY FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(17, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(19, 12) = "" Then
          MsgBox "ASSEMBLY USE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(21, 12) = "" Then
          MsgBox "MODEL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(22, 12) = "" Then
          MsgBox "SERIAL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(23, 12) = "" Then
          MsgBox "LINE PRESSURE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(45, 11) = "" Then
          MsgBox "QUESTION #1 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(45, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(46, 11) = "" Then
          MsgBox "QUESTION #2 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(46, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(47, 11) = "" Then
          MsgBox "QUESTION #3 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(47, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(48, 11) = "" Then
          MsgBox "QUESTION #4 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(48, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(49, 11) = "" Then
          MsgBox "QUESTION #5 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(49, 11)
         End If
        
        If Cells(29, 5).Value > "" And Cells(50, 11) = "" Then
          MsgBox "BACKFLOW PASS/FAIL missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(50, 11)
         End If
Next ws

End Sub
 
Upvote 0
You're missing an 'end if' right before the Next command. Add that and you should be gold.
 
Upvote 0
Candyman8019 said:
You're missing an 'end if' right before the Next command. Add that and you should be gold.
Click to expand...
I tried that and it still gives me "Next without For". Do I need to add a For Each loop for every function within my code? If so I don't think my code will work as anticipated
 
Upvote 0
It runs on my end without error. Here's the full script:

VBA Code: 
Private Sub test()
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "BF" Then
       If Cells(17, 5) = "Reduced Pressure" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
        End If
         
       If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
         
        If Cells(17, 5) = "Reduced Pressure" And Cells(33, 5) = "" Then
          MsgBox "RELIEF VALVE missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(33, 5)
         End If
         
        If Cells(17, 5) = "Double Check" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
         
        If Cells(17, 5) = "Double Check" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
         
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
         
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(37, 5) = "" Then
          MsgBox "AIR INLET missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(37, 5)
         End If
         
        If Cells(17, 5).Value > "" And Cells(39, 6) = "" Then
          MsgBox "SHUT OFF VAVLE #1 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 6)
         End If
         
        If Cells(17, 5).Value > "" And Cells(39, 14) = "" Then
          MsgBox "SHUT OFF VAVLE #2 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 14)
         End If
         
        If Cells(29, 5).Value > "" And Cells(19, 5) = "" Then
          MsgBox "ASSEMBLY TEST FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(21, 5) = "" Then
          MsgBox "MANUFACTURER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(22, 5) = "" Then
          MsgBox "SIZE FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(23, 5) = "" Then
          MsgBox "LOCATION missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 5)
         End If
         
        If Cells(29, 5).Value > "" And Cells(17, 12) = "" Then
          MsgBox "ASSEMBLY FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(17, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(19, 12) = "" Then
          MsgBox "ASSEMBLY USE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(21, 12) = "" Then
          MsgBox "MODEL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(22, 12) = "" Then
          MsgBox "SERIAL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(23, 12) = "" Then
          MsgBox "LINE PRESSURE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 12)
         End If
         
        If Cells(29, 5).Value > "" And Cells(45, 11) = "" Then
          MsgBox "QUESTION #1 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(45, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(46, 11) = "" Then
          MsgBox "QUESTION #2 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(46, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(47, 11) = "" Then
          MsgBox "QUESTION #3 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(47, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(48, 11) = "" Then
          MsgBox "QUESTION #4 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(48, 11)
         End If
          
        If Cells(29, 5).Value > "" And Cells(49, 11) = "" Then
          MsgBox "QUESTION #5 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(49, 11)
         End If
        
        If Cells(29, 5).Value > "" And Cells(50, 11) = "" Then
          MsgBox "BACKFLOW PASS/FAIL missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(50, 11)
         End If
    End If
Next ws

End Sub
 
Upvote 0
Candyman8019 said:
It runs on my end without error. Here's the full script:

VBA Code: 
Private Sub test()
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "BF" Then
       If Cells(17, 5) = "Reduced Pressure" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
        End If
        
       If Cells(17, 5) = "Reduced Pressure" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
        
        If Cells(17, 5) = "Reduced Pressure" And Cells(33, 5) = "" Then
          MsgBox "RELIEF VALVE missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(33, 5)
         End If
        
        If Cells(17, 5) = "Double Check" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
        
        If Cells(17, 5) = "Double Check" And Cells(31, 5) = "" Then
          MsgBox "CHECK #2 missing value Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(31, 5)
         End If
        
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(29, 5) = "" Then
          MsgBox "CHECK #1 missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(29, 5)
         End If
        
        If Cells(17, 5) = "Pressure Vacuum Breaker" And Cells(37, 5) = "" Then
          MsgBox "AIR INLET missing value on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(37, 5)
         End If
        
        If Cells(17, 5).Value > "" And Cells(39, 6) = "" Then
          MsgBox "SHUT OFF VAVLE #1 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 6)
         End If
        
        If Cells(17, 5).Value > "" And Cells(39, 14) = "" Then
          MsgBox "SHUT OFF VAVLE #2 missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(39, 14)
         End If
        
        If Cells(29, 5).Value > "" And Cells(19, 5) = "" Then
          MsgBox "ASSEMBLY TEST FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 5)
         End If
        
        If Cells(29, 5).Value > "" And Cells(21, 5) = "" Then
          MsgBox "MANUFACTURER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 5)
         End If
        
        If Cells(29, 5).Value > "" And Cells(22, 5) = "" Then
          MsgBox "SIZE FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 5)
         End If
        
        If Cells(29, 5).Value > "" And Cells(23, 5) = "" Then
          MsgBox "LOCATION missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 5)
         End If
        
        If Cells(29, 5).Value > "" And Cells(17, 12) = "" Then
          MsgBox "ASSEMBLY FOR missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(17, 12)
         End If
        
        If Cells(29, 5).Value > "" And Cells(19, 12) = "" Then
          MsgBox "ASSEMBLY USE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(19, 12)
         End If
        
        If Cells(29, 5).Value > "" And Cells(21, 12) = "" Then
          MsgBox "MODEL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(21, 12)
         End If
        
        If Cells(29, 5).Value > "" And Cells(22, 12) = "" Then
          MsgBox "SERIAL NUMBER missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(22, 12)
         End If
        
        If Cells(29, 5).Value > "" And Cells(23, 12) = "" Then
          MsgBox "LINE PRESSURE missing info on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(23, 12)
         End If
        
        If Cells(29, 5).Value > "" And Cells(45, 11) = "" Then
          MsgBox "QUESTION #1 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(45, 11)
         End If
         
        If Cells(29, 5).Value > "" And Cells(46, 11) = "" Then
          MsgBox "QUESTION #2 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(46, 11)
         End If
         
        If Cells(29, 5).Value > "" And Cells(47, 11) = "" Then
          MsgBox "QUESTION #3 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(47, 11)
         End If
         
        If Cells(29, 5).Value > "" And Cells(48, 11) = "" Then
          MsgBox "QUESTION #4 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(48, 11)
         End If
         
        If Cells(29, 5).Value > "" And Cells(49, 11) = "" Then
          MsgBox "QUESTION #5 missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(49, 11)
         End If
       
        If Cells(29, 5).Value > "" And Cells(50, 11) = "" Then
          MsgBox "BACKFLOW PASS/FAIL missing on Worksheets.Name", vbExclamation, "Dignity Fire Protection"
          Cancel = True
          Application.Goto Cells(50, 11)
         End If
    End If
Next ws

End Sub
Click to expand...
How are you testing the code? I am testing by attempting the save as in the workbook to run a real life simulation. And with that all I get is "Next without For"
 
Upvote 0
Just wanted to update. Here is the fixed code. Thank you for your help. You mentioned about having the End If before Next. That fixed it along with adding ws. before Cells(x, x)

[CODE = vba]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(10, 2) = "" Then
MsgBox "ADDRESS Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(10, 2)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(11, 2) = "" Then
MsgBox "CITY Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(11, 2)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(11, 5) = "" Then
MsgBox "ZIP CODE Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(11, 5)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(5, 9) = "" Then
MsgBox "DATE Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(5, 9)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(9, 9) = "" Then
MsgBox "JOB NUMBER Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(9, 9)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(10, 9) = "" Then
MsgBox "YOUR NAME(INSPECTED BY) Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(10, 9)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(11, 13) = "" Then
MsgBox "YOUR CERT NUMBER Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(11, 13)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(11, 9) = "" Then
MsgBox "TEST TYPE Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(11, 9)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(12, 9) = "" Then
MsgBox "CERTIFICATION EXPIRATION Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(12, 9)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(12, 12) = "" Then
MsgBox "GAUGE SERIAL NUMBER Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(12, 12)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(13, 11) = "" Then
MsgBox "GAUGE RECALIBRATION DATE Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(13, 11)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(16, 1) = "" Then
MsgBox "SERVICE CALL QUANTITY Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(16, 1)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(16, 11) = "" Then
MsgBox "SERVICE CALL $ AMOUNT Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(16, 11)
End If

If Worksheets("Service Receipt").Cells(17, 1) > "0" And Worksheets("Service Receipt").Cells(17, 11) = "" Then
MsgBox "BACKFLOW $ AMOUNT Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(17, 11)
End If

If Worksheets("Service Receipt").Cells(9, 2) > "" And Worksheets("Service Receipt").Cells(53, 1) = "" Then
MsgBox "NOTES TO OFFICE Missing on Service Receipt", vbInformation, "Dignity Fire Protection"
Cancel = True
Application.Goto Worksheets("Service Receipt").Cells(53, 1)
End If


Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "*BF*" Then

If ws.Cells(17, 5) = "Reduced Pressure" And ws.Cells(29, 5) = "" Then
MsgBox "CHECK #1 missing value on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(29, 5)
End If

If ws.Cells(17, 5) = "Reduced Pressure" And ws.Cells(31, 5) = "" Then
MsgBox "CHECK #2 missing value on (ws.Name)", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(31, 5)
End If

If ws.Cells(17, 5) = "Reduced Pressure" And ws.Cells(33, 5) = "" Then
MsgBox "RELIEF VALVE missing value ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(33, 5)
End If

If ws.Cells(17, 5) = "Double Check" And ws.Cells(29, 5) = "" Then
MsgBox "CHECK #1 missing value ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(29, 5)
End If

If ws.Cells(17, 5) = "Double Check" And ws.Cells(31, 5) = "" Then
MsgBox "CHECK #2 missing value ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(31, 5)
End If

If ws.Cells(17, 5) = "Pressure Vacuum Breaker" And ws.Cells(29, 5) = "" Then
MsgBox "CHECK #1 missing value on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(29, 5)
End If

If ws.Cells(17, 5) = "Pressure Vacuum Breaker" And ws.Cells(37, 5) = "" Then
MsgBox "AIR INLET missing value on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(37, 5)
End If

If ws.Cells(17, 5).Value > "" And ws.Cells(39, 6) = "" Then
MsgBox "SHUT OFF VAVLE #1 missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(39, 6)
End If

If ws.Cells(17, 5).Value > "" And ws.Cells(39, 14) = "" Then
MsgBox "SHUT OFF VAVLE #2 missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(39, 14)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(19, 5) = "" Then
MsgBox "ASSEMBLY TEST FOR missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(19, 5)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(21, 5) = "" Then
MsgBox "MANUFACTURER missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(21, 5)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(22, 5) = "" Then
MsgBox "SIZE FOR missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(22, 5)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(23, 5) = "" Then
MsgBox "LOCATION missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(23, 5)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(17, 12) = "" Then
MsgBox "ASSEMBLY FOR missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(17, 12)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(19, 12) = "" Then
MsgBox "ASSEMBLY USE missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(19, 12)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(21, 12) = "" Then
MsgBox "MODEL NUMBER missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(21, 12)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(22, 12) = "" Then
MsgBox "SERIAL NUMBER missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(22, 12)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(23, 12) = "" Then
MsgBox "LINE PRESSURE missing info on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(23, 12)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(45, 11) = "" Then
MsgBox "QUESTION #1 missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(45, 11)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(46, 11) = "" Then
MsgBox "QUESTION #2 missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(46, 11)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(47, 11) = "" Then
MsgBox "QUESTION #3 missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(47, 11)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(48, 11) = "" Then
MsgBox "QUESTION #4 missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(48, 11)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(49, 11) = "" Then
MsgBox "QUESTION #5 missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(49, 11)
End If

If ws.Cells(29, 5).Value > "" And ws.Cells(50, 11) = "" Then
MsgBox "BACKFLOW PASS/FAIL missing on ws.Name", vbExclamation, "Dignity Fire Protection"
Cancel = True
Application.Goto ws.Cells(50, 11)
End If
End If

Next ws

End Sub
[CODE/]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Can't get VBA code to prevent "Save As" from working with this code
2
Replies
12
Views
844
Chassee
C
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
351
Agnarr
Agnarr
M
  • Question
Get Data From Sheets to MasterSheet and keep All sheets updated if Something is updated
Replies
1
Views
407
MTennis
M
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
283
offthelip
O
S
  • Solved
VBA copy template sheet to new monthly workbook with daily sheets tabname and header A2:J2 named as "dd mm yyyy" + autosave filename predefined
Replies
4
Views
585
mumps
mumps

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top