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

Moseth

New Member
Joined
Sep 5, 2018
Messages
12
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
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

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,698
Office Version
365
Platform
Windows
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)
Code:
IF Sheets("WERS ALERT SHEET V14").Range("[COLOR=#ff0000]Z1[/COLOR]").Value = TRUE Then
  MsgBox "Some mandatory cells not completed"
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Another option
Code:
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
 

Forum statistics

Threads
1,082,316
Messages
5,364,491
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top