VBA help - error message when line isn't filled out

  • Thread starter Thread starter moily
  • Start date Start date
M

moily

Hi there,

I'm not very good at VBA and would appreciate some help on an issue I need
to resolve. The basic scenario is this:

This is a workbook that tracks holidays for a department. Each individual
has their own worksheet that is set up identically to everyone else's. There
are a few other worksheets in the workbook that must be be ignored.

The individual sheets will have data entered into them on an ad hoc basis in
either of two ranges. The first, B17:D62, is a full block of data but the
second, J17:L62 and O17:O62, skips two columns (M and N) in between.

When a cell has been filled out in a given worksheet all the other cells in
that row in that block in that worksheet must also be filled out. ie if B17
in worksheet Smith is filled out then C17 and D17 in the worksheet Smith must
also be filled in. If cell J17 is filled out in worksheet Jones then K17,
L17, O17 in worksheet Jones must also be filled in. If the row hasn't been
completed then the user shouldn't be allowed to close the file until it is
completed. They should get a warning box saying that all the information has
not been fully entered and the missing cells (along with the name of the
worksheet they are located in) should be listed.

Can anybody help? Given that I'm not very good at VBA but am trying to
learn I'd truly appreciate if you put notes as to how it's working so I can
learn it. Much appreciated in advance if you can!

Cheers,
Ann
 
moily,

You can use the Excel's before save event to check that either the rows of
each area are fully blank or filled in.

Copy the code below, double-click the ThisWorkbook object in the project
explorer, and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim myR As Range
Dim sh As Worksheet
Dim myC As Integer

For Each sh In Worksheets 'loop through all sheets

' loop through the rows of B:D
For Each myR In sh.Range("B17:B62")
myC = Application.CountBlank(myR.Resize(1, 3))
If myC <> 0 And myC <> 3 Then 'either they are all filled in or
all blank
Cancel = True 'stop the save and tell the user what needs
to be filled in
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 3).Cells.Address
Exit Sub
End If
Next myR

' loop through the rows of J,K,L,O
For Each myR In sh.Range("J17:J62")
myC = Application.CountBlank(myR.Resize(1, 3)) + _
Application.CountBlank(Intersect(myR.EntireRow,
sh.Range("O:O")))
If myC <> 0 And myC <> 4 Then
Cancel = True
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 6).Cells.Address
Exit Sub
End If
Next myR
Next sh
End Sub
 
This is brilliant Bernie thanks - however, there's just that one other
thing... I need to skip the sheet if it's not one of the 'individual' sheets.
I've ammended yours below as follows to look for a criteria in each sheet
before it goes about your work otherwise to skip the sheet but i'm getting an
error saying the 'next' of the skipit needs a 'for'... do you know how i can
fix it?

Cheers!
Ann

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myR As Range
Dim sh As Worksheet
Dim myC As Integer

For Each sh In Worksheets 'loop through all sheets

'If there is an initial in cell b1 then continue otherwise ignore
If sh.Range("A1").Value = "Initials" Or _
sh.Range("A1").Value = "" Then GoTo skipIt

' loop through the rows of B:D
For Each myR In sh.Range("B17:B62")
myC = Application.CountBlank(myR.Resize(1, 3))
If myC <> 0 And myC <> 3 Then 'either they are all filled in or
all blank
Cancel = True 'stop the save and tell the user what needs
to be filled in
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 3).Cells.Address
Exit Sub
End If
Next myR
skipIt:
Next sh

'If there is an initial in cell b1 then continue otherwise ignore
If sh.Range("A1").Value = "Initials" Or _
sh.Range("A1").Value = "" Then GoTo skipIt

' loop through the rows of J,K,L,O
For Each myR In sh.Range("J17:J62")
myC = Application.CountBlank(myR.Resize(1, 3)) + _
Application.CountBlank(Intersect(myR.EntireRow,
sh.Range("O:O")))
If myC <> 0 And myC <> 4 Then
Cancel = True
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 6).Cells.Address
Exit Sub
End If
Next myR
Next sh

skipIt:
Next sh

End Sub
 
Ann,

You only need one SkipIt: label... try it like below.

HTH,
Bernie
MS Excel MVP


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myR As Range
Dim sh As Worksheet
Dim myC As Integer

For Each sh In Worksheets 'loop through all sheets

'If there is an initial in cell b1 then continue otherwise ignore
If sh.Range("A1").Value = "Initials" Or _
sh.Range("A1").Value = "" Then GoTo skipIt

' loop through the rows of B:D
For Each myR In sh.Range("B17:B62")
myC = Application.CountBlank(myR.Resize(1, 3))
If myC <> 0 And myC <> 3 Then 'either they are all filled in or all blank
Cancel = True 'stop the save and tell the user what needs to be filled in
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 3).Cells.Address
Exit Sub
End If
Next myR

' loop through the rows of J,K,L,O
For Each myR In sh.Range("J17:J62")
myC = Application.CountBlank(myR.Resize(1, 3)) + _
Application.CountBlank(Intersect(myR.EntireRow, sh.Range("O:O")))
If myC <> 0 And myC <> 4 Then
Cancel = True
MsgBox "You only partly filled in " & _
sh.Name & " cells " & myR.Resize(1, 6).Cells.Address
Exit Sub
End If
Next myR

skipIt:
Next sh

End Sub
 
Back
Top