before save event, need help for looping two cells at the same time

Question

I'm trying to do a beforesave event, not allowing users to save if one of two given cells are empty. What I managed to do so far is linking column 13 (M) and cell A4.

What I'd like to do is applying the event to a combination of two range and rows, A4-A19 and M4-M19. In this way: If A4 is not empty and M4 is empty, a msgbox appears and blocks saving and so on..A5-M5, A6-M6...until A19-M19. If both corresponding cells are empty at the same time, then saving should be possible.

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

    Dim i As Integer, MyWb As Object
    i = 13
    Set MyWb = ThisWorkbook.Sheets("Planning").Cells
    Do While MyWb(4, i).Value <> ""
    i = i + 1
    Loop
    If i = 13 Then
        If ThisWorkbook.Sheets("Planning").Range("A4") <> "" Then
            MsgBox ("You will need to enter topics before saving"), vbCritical
            Cancel = True
        End If
    End If
End Sub

Based on Wolfie's code, I managed to obtain what I wanted, just adding a If not isempty for A column and replacing 19 instead of 13.

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

Dim plansht As Worksheet
Set plansht = ThisWorkbook.Sheets("Planning")

' Loop over rows
Dim rw As Integer
For rw = 4 To 19
    ' Test if both the A and M column in row "rw" are blank
    If Not IsEmpty(plansht.Range("A" & rw)) And plansht.Range("M" & rw).Value = "" Then
        MsgBox ("You will need to enter topics before saving"), vbCritical
        Cancel = True
    End If
Next rw

End Sub

The code you've created from my answer checks: "If Ai is not empty and Mi is empty then ...", note that the `Not` only applies to the first condition before the `And`! I'm not sure if that's what you want? — Wolfie, Feb 21 '17 at 15:51
yes it is exactly what I wanted, when I got your first answers I realised that my original request was not correct...in reality I wanted exactly this behaviour: if A is not empty and M is empty then.......:) — thom80, Feb 21 '17 at 15:58
Ah okay, thanks to be of help, I've edited my answer for some notes on using the logical operations :) — Wolfie, Feb 21 '17 at 16:03

Séb Cô · Answer 1 · 2017-02-21T16:38:12.833

1

Try this :

For i = 4 to 19
    If ThisWorkbook.Sheets("Planning").Range("A" & i) <> "" AND _
       ThisWorkbook.Sheets("Planning").Range("M" & i) <> ""  Then
          MsgBox("Hey bro you didn't wrote properly on line " & i)
          Cancel = True

Next i

edited Feb 21 '17 at 16:38

answered Feb 21 '17 at 14:53

Séb Cô

79
1
8

`For 1 = 4 to 19` should probably be `For i = 4 to 19`. Typos are fun! :) – FreeMan Feb 21 '17 at 15:01
Thanks for this typo :) – Séb Cô Feb 21 '17 at 16:38

score 0 · Answer 2 · answered Feb 21 '17 at 14:45

Give this a try:

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

    Const FIRST_ROW as integer = 4
    Const LAST_ROW as integer = 19
    Const ColA As Integer = 4
    Const ColM as integer = 13
    Dim MyWb As Worksheet
    Dim CurRow as Integer

    Set MyWb = ThisWorkbook.Sheets("Planning")
    For CurRow = FIRST_ROW to LAST_ROW
      If len(mywb.cells(CurRow, ColA)) = 0 and len(mywb.cells(CurRow, ColM)) = 0 then
        MsgBox ("You will need to enter topics before saving"), vbCritical
        Cancel = True
        Exit For
      End If
    Next
End Sub

Untested code (I can never remember if it's (Row,Col) or (Col,Row)), but I think that will get you what you're after. This will abort the save on the first pair where they're both blank.

If this works, you could get fancy and highlight the blank pair(s), remove the highlight for OK pairs (in case they were highlighted previously), processing through all the pairs and removing the Exit For and providing one error message that anything highlighted needs to be looked after.

I've been told that Len(string) = 0 is a faster way to determine that a string is null than string = vbNullString. No guarantees as to suitability to purpose, but that's what I've learned.

It works but I realised that my description is wrong...I will edit it. — thom80, Feb 21 '17 at 14:59
You're correct, it is `Cells(row, col)`, easy to remember because it's the opposite of `Range` ;-) You can also reference within `Cells` by column letter, so instead of `ColA` and `ColM` you can just use `"A"` and `"M"` :) — Wolfie, Feb 21 '17 at 15:00

Wolfie · Accepted Answer · 2017-02-21T16:03:14.953

You can loop over the rows, and just test the A and M columns to test if they are both blank for a given row. See the below code...

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

Dim plansht as Worksheet
Set plansht = ThisWorkbook.Sheets("Planning")

' Loop over rows
Dim rw as Integer
For rw = 4 to 13  
    ' Test if both the A and M column in row "rw" are blank
    If plansht.Range("A" & rw).Value = "" And plansht.Range("M" & rw).Value = "" Then
        MsgBox ("You will need to enter topics before saving"), vbCritical
        Cancel = True   
    End If    
Next rw

End Sub

Edit:

Your edit suggested you want some different combinations of the cells being empty. Here are some examples of the If statement for different outcomes

' If BOTH are empty
If plansht.Range("A" & rw).Value = "" And plansht.Range("M" & rw).Value = "" Then ...

If IsEmpty(plansht.Range("A" & rw)) And IsEmpty(plansht.Range("M" & rw)) Then ...

' If EITHER is empty
If plansht.Range("A" & rw).Value = "" OR plansht.Range("M" & rw).Value = "" Then ...

If IsEmpty(plansht.Range("A" & rw)) Or IsEmpty(plansht.Range("M" & rw)) Then ...

' If BOTH are NOT empty
If plansht.Range("A" & rw).Value <> "" And plansht.Range("M" & rw).Value <> "" Then ...

If Not IsEmpty(plansht.Range("A" & rw)) And Not IsEmpty(plansht.Range("M" & rw)) Then ...

Notice that when you start introducing Not with multiple conditions, the logic can quickly become hard to interpret. You can use brackets to group conditions with a Not, but you get things like this meaning the same logically:

If Not IsEmpty(plansht.Range("A" & rw)) And Not IsEmpty(plansht.Range("M" & rw)) Then ...
If Not (IsEmpty(plansht.Range("A" & rw)) Or IsEmpty(plansht.Range("M" & rw))) Then ...

I've just realised that my description was not correct, what I'm trying to do is the opposite. I edited the description. — thom80, Feb 21 '17 at 15:11
For either being empty (instead of *both*) just use `Or` in place of `And`! :) — Wolfie, Feb 21 '17 at 15:48

before save event, need help for looping two cells at the same time

3 Answers3