is there an easier way to make loads of cells mandatory then coding out for them separately?

[Moseth]

Hi, I want to make a lot of cells mandTORY, iS THERE AN EASIER WAY TO DO THIS RATHER THAN THIS CODE: After this can put no more code in as it says too many line continuations


If Application.Sheets("WERS ALERT SHEET V14").Range("C5").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("G5").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("J5").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("C7").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("C8").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("I8").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("F9").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D11").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("G12").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D15").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("C16").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("C17").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("E18").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("E19").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("E20").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("H21").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("E24").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("E25").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D26").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D27").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("C29").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D31").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("D32").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("G32").Value = "" Or _
Application.Sheets("WERS ALERT SHEET V14").Range("J32").Value = "" Then

 

[Yongle]

One method:
- add more cell references to the array each one separated by a comma

This simply builds up a message string to tell the use which cells are empty

Sub Test()

Dim cel As Variant, msg As String

For Each cel In Array("C5", "G5", "J5", "C7")
    Set cel = Sheets("WERS ALERT SHEET V14").Range(cel)
    If cel.Value = "" Then
        msg = msg & " " & cel.Address(0, 0)
    End If
Next
MsgBox msg, , "CELLS WITHOUT VALUES"
End Sub

 

[Yongle]

and to make the user aware that certain cells are mandatory, use conditional formatting like this...

1. select all the mandatory cells
- if you hold down the {CTRL} key you can click on each one in turn
2. Home Tab \ Conditional Formatting \ New Rule \ Use formula to determine which cell \
use this formula (where C3 is the top left cell of all the cells selected)
=C3=""
and format the cells with a red Fill (or whatever you prefer)

Each cell remains filled in red until the cell contains a value

 

[Yongle]

Another way would be to use a helper cell to hold a formula which returns TRUE if any of the cells contain no value
=OR(C5 = "",G5 = "",J5 = "",C7 = "",C8 = "",I8 = "",F9 = "",D11 = "",G12 = "",D15 = "",C16 = "",C17 = "",E18 = "",E19 = "",E20 = "",H21 = "",E24 = "",E25 = "",D26 = "",D27 = "",C29 = "",D31 = "",D32 = "",G32 = "",J32 = "")

and then the VBA is really simple (where Z1 contains the above formula)

IF Sheets("WERS ALERT SHEET V14").Range("[COLOR=#ff0000]Z1[/COLOR]").Value = TRUE Then
  MsgBox "Some mandatory cells not completed"
End If

 

[Fluff]

Another option

With Sheets("WERS ALERT SHEET V14")
   If Application.CountA(.Range("C5,G5,J5,C7,C8,I8")) <> 6 Then
      MsgBox "Enter all data"
      Exit Sub
   End If
End With