在保存事件之前，需要帮助同时循环两个单元格

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.

我正在尝试进行beforesave事件,如果两个给定单元格中的一个为空,则不允许用户保存。到目前为止,我设法做的是连接第13列(M)和第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.

我想要做的是将事件应用于两个范围和行的组合,A4-A19和M4-M19。这样:如果A4不为空并且M4为空,则会出现一个msgbox并阻止保存等等.A5-M5,A6-M6 ......直到A19-M19。如果两个相应的单元格同时为空,则应该可以保存。

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.

基于Wolfie的代码,我设法获得了我想要的东西,只需为A列添加一个If not isempty并替换19而不是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

3 个解决方案

#1

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...

您可以循环遍历行,只测试A和M列以测试它们对于给定行是否都是空白的。见下面的代码......

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语句的一些示例

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

请注意,当您开始介绍Not具有多个条件时,逻辑很快就会难以解释。您可以使用括号将条件分组为Not,但是您可以在逻辑上得到相同的含义:

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 ...

#2

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

#3

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.

未经测试的代码(我永远不会记得它是(Row,Col)还是(Col,Row)),但我认为这会让你得到你想要的东西。这将中止第一对上的保存,它们都是空白的。

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.

如果这样可行,您可以获得幻想并突出显示空白对,删除OK对的突出显示(如果之前已突出显示),处理所有对并删除Exit For并提供一条错误消息,指出任何突出显示的内容需要照顾。

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.

我被告知Len(string)= 0是一种更快的方法来确定字符串是否为null = string = vbNullString。不能保证适合目的,但这就是我所学到的。

#1