1

I have an Excel file where I want people to fill in specific stuff. I want to make a range of cells in a row mandatory if the first cell in the row is filled in. E.g. if cell A7 is filled in, cells B7-O7 need to be filled in. And this repeats itself until there is a cell in column A that's not filled in.

I tried some not very good VBA coding here

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If Sheets("Sheet1").Range("A7").Value <> "" And Sheets("Sheet1").Range("B7").Value = "" Or Range("C7").Value = "" Or Range("D7").Value = "" Or Range("E7").Value = "" Or Range("F7").Value = "" Or Range("G7").Value = "" Or Range("H7").Value = "" Or Range("I7").Value = "" Or Range("J7").Value = "" Or Range("K7").Value = "" Or Range("L7").Value = "" Or Range("M7").Value = "" Or Range("N7").Value = "" Or Range("O7").Value = "" Then
        MsgBox "Alla celler i en rad måste vara ifyllda för att du skall kunna spara. Kontrollera detta och spara igen."
        Cancel = True

    ElseIf Sheets("Sheet1").Range("A8").Value <> "" And Sheets("Sheet1").Range("B8").Value = "" Or Range("C8").Value = "" Or Range("D8").Value = "" Or Range("E8").Value = "" Or Range("F8").Value = "" Or Range("G8").Value = "" Or Range("H8").Value = "" Or Range("I8").Value = "" Or Range("J8").Value = "" Or Range("K8").Value = "" Or Range("L8").Value = "" Or Range("M8").Value = "" Or Range("N8").Value = "" Or Range("O8").Value = "" Then
        MsgBox "Alla celler i en rad måste vara ifyllda för att du skall kunna spara. Kontrollera detta och spara igen."
        Cancel = True

    ElseIf Sheets("Sheet1").Range("A9").Value <> "" And Sheets("Sheet1").Range("B9").Value = "" Or Range("C9").Value = "" Or Range("D9").Value = "" Or Range("E9").Value = "" Or Range("F9").Value = "" Or Range("G9").Value = "" Or Range("H9").Value = "" Or Range("I9").Value = "" Or Range("J9").Value = "" Or Range("K9").Value = "" Or Range("L9").Value = "" Or Range("M9").Value = "" Or Range("N9").Value = "" Or Range("O9").Value = "" Then
        MsgBox "Alla celler i en rad måste vara ifyllda för att du skall kunna spara. Kontrollera detta och spara igen."
        Cancel = True

    End If

End Sub

It works until I get to:

ElseIf Sheets("Sheet1").Range("A9").Value <> "" And Sheets("Sheet1").Range("B9").Value = "" Or Range("C9").Value = "" Or Range("D9").Value = "" Or Range("E9").Value = "" Or Range("F9").Value = "" Or Range("G9").Value = "" Or Range("H9").Value = "" Or Range("I9").Value = "" Or Range("J9").Value = "" Or Range("K9").Value = "" Or Range("L9").Value = "" Or Range("M9").Value = "" Or Range("N9").Value = "" Or Range("O9").Value = "" Then
    MsgBox "Alla celler i en rad måste vara ifyllda för att du skall kunna spara. Kontrollera detta och spara igen."
    Cancel = True
End If

Then the MsgBox pops up even though A9 is not filled in.

I know this is not the best way to achieve what I want to but it was what I came up with. Any suggestions for better code?

  • Just a suggestion: instead of Sheets("Sheet1").Range("B9").Value = "" Or Range("C9").Value = "" Or Range("D9").Value = "" Or Range("E9").Value = "" Or Range("F9").Value = "" Or Range("G9").Value = "" Or Range("H9").Value = "" Or Range("I9").Value = "" Or Range("J9").Value = "" Or Range("K9").Value = "" Or Range("L9").Value = "" Or Range("M9").Value = "" Or Range("N9").Value = "" Or Range("O9").Value = "" you can use Worksheetfunction.countif(Sheets("Sheet1").Range("B9:O9"),"=")=0 – Máté Juhász Sep 13 '18 at 7:34
  • another thing: you used Sheets("Sheet1") only for first references, that's very risky as others might change context. – Máté Juhász Sep 13 '18 at 7:35
  • Why don't you try DATA VALIDATION is much easier !! – Rajesh S Sep 13 '18 at 7:41
  • @RajeshS Sorry. Dind't mention that I already use Data validation to control if the content in the cells follows some criterias. Can I have more then one data validation in one cell? – Stax82 Sep 13 '18 at 8:26
  • @Stax82, since you are working row by row then each row needs separate Validation. – Rajesh S Sep 13 '18 at 8:57
2

You've got an error in your chain of conditions:

cond1 And cond2 Or cond3 Or cond4 will always evaluate to True if one (or both) of cond3 or cond4 are True. This is because And will be evaluated before Or. See the documentation for more information.

You can employ parentheses to adjust the evaluation order:

cond1 And (cond2 Or cond3 Or cond4)

This expression will evaluate to True only if cond1 and any of cond2, cond3, cond4 are True.


In your case I'd recommend the following improvements to your code:

  • Firstly, read up about loops in VBA. For ... Next and Do ... Loop are the two types you need to know about.
  • Then, to make things a bit clearer (that is, let your intentions be visible in the code), put that validation code into its own subroutine/function.
  • And finally to make it even more obvious what's happening, you can split up these long chains of conditions.

If we right now look at the Workbook_BeforeSave procedure, it's not immediately obvious what it is doing there (it might be to you, because you just put the code there and it's fresh on your mind - but come back to this sheet in 3/6/12 months and you'll first need to understand what it's doing). Let's remedy that:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Cancel = IsUserInputMissing

End Sub

Private Function IsUserInputMissing() as Boolean

    ' Validation code goes in here

End Function

Now everyone looking at the code can tell at a glance what will happen when the workbook is saved.

This line

If Sheets("Sheet1").Range("A7").Value <> "" And Sheets("Sheet1").Range("B7").Value = "" Or Range("C7").Value = "" Or Range("D7").Value = "" Or Range("E7").Value = "" Or Range("F7").Value = "" Or Range("G7").Value = "" Or Range("H7").Value = "" Or Range("I7").Value = "" Or Range("J7").Value = "" Or Range("K7").Value = "" Or Range("L7").Value = "" Or Range("M7").Value = "" Or Range("N7").Value = "" Or Range("O7").Value = "" Then

is not very friendly on the eyes. Verbatim it says If FirstCellIsNotEmpty And AnyFollowingCellIsEmpty Then. Let's code it this way.

Dim FirstCellIsEmpty as Boolean
FirstCellIsEmpty = Sheets("Sheet1").Range("A7").Value = ""

Dim AnyFollowingCellIsEmpty as Boolean
AnyFollowingCellIsEmpty = WorksheetFunction.CountBlank(Sheets("Sheet1").Range("B7:O7")) > 0

If Not FirstCellIsEmpty And AnyFollowingCellIsEmpty Then
    MsgBox "I don't know any Swedish. But please fill out the necessary cells."
End If

Note the use of WorksheetFunction.CountBlank to avoid having to type out every single cell to be checked.


In the end, I'll give an example of how your IsUserInputMissing function could look like. (There is still plenty of room for improvement, though.)

Private Function IsUserInputMissing() As Boolean

    ' Easy way to set the beginning of the range
    Const FirstRowToBeChecked As Long = 7
    ' Set a reference to the sheet that needs checking
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("Sheet1")

    Dim iRow As Long
    iRow = FirstRowToBeChecked

    Do

        Dim FirstCellIsEmpty As Boolean
        FirstCellIsEmpty = Ws.Cells(iRow, 1).Value = vbNullString ' vbNullString is a clearer way of saying ""
        ' Exit loop at the first empty row
        If FirstCellIsEmpty Then Exit Do

        Dim AnyFollowingCellIsEmpty As Boolean
        AnyFollowingCellIsEmpty = WorksheetFunction.CountBlank(Ws.Range(Ws.Cells(iRow, 2), Ws.Cells(iRow, 15))) > 0

        If AnyFollowingCellIsEmpty Then
            ' Any time the requirements are not met, notify user and abort checking and saving
            ' This is not as elegant as checking the whole range and collecting info about all missing inputs
            ' But it's way easier to code :)
            MsgBox "I don't know any Swedish. But please fill out the necessary cells. Tack!"
            IsUserInputMissing = True
            Exit Function
        End If
        ' Don't forget to increment the counter, otherwise you've got yourself an endless loop
        iRow = iRow + 1

    Loop

    ' If execution reaches this line, all rows fulfil the requirement
    ' IsUserInputMissing will have its Default value: False
End Function

Take your time and work through it. I've tried to make things as clear as possible. But if something just isn't clear. Don't hesitate to ask. :)

  • Thank you for your help. Now i will develop this code to do a little bit more. – Stax82 Sep 13 '18 at 11:44

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.