If one cell in range is not empty, the all range must be not empty.

[loroverde]

Hello
I´m trying to do this in VB:
If one (or any) cell in the range A1:A6 is not empty, then all range cells must be not empty.
If not, cancel save and show msg box.

Please help me.
Regards
Claudio.

[patel45]

Sub b()
s = Application.WorksheetFunction.CountIf(Range("A1:A6"), "")
If s < 6 And s > 0 Then
  MsgBox "some cell empty"
End If
End Sub

[HaHoBe]

Hiu, patel45,

If not, cancel save and show msg box.

This macro must be part of Workbook_BeforeClose-event - please read all the post carefully.

@loroverde:
adjust the name of the sheet to suit and place this code in ThisWorkbbok:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
  If WorksheetFunction.CountBlank(.Range("A1:A6")) < 6 Then
    If WorksheetFunction.CountA(.Range("A1:A6")) < 6 Then
      Cancel = True
      MsgBox "All cells on '" & .Name & "' in 'A1:A6' must be filled!", vbExclamation, "Abort Closing"
    End If
  End If
End With
End Sub

Ciao,
Holger

[loroverde]

Hello
It works great as a macro, but i want to cancel before save if it doesn't accomplish the condition, I have used cancel=true but it doesn't work.
how can I do this.
Regards
claudio.

[HaHoBe]

Hi. loroverde,

sorry about my guess for a wrong event - maybe use Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) in Thisworkbook instead:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet1")
  If WorksheetFunction.CountBlank(.Range("A1:A6")) < 6 Then
    If WorksheetFunction.CountA(.Range("A1:A6")) < 6 Then
      Cancel = True
      MsgBox "All cells on '" & .Name & "' in 'A1:A6' must be filled!", vbExclamation, "Abort Saving"
    End If
  End If
End With
End Sub

Ciao,
Holger

[loroverde]

Hello holger
It works fine, but how can i do If one (or any) cell in the range A1:F1 and the cell H1 are not empty, then all range cells must be not empty.
If not, cancel save and show msg box.


Regards
Claudio.

[HaHoBe]

Hi, Claudio,

maybe like this:

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

Dim rngCheck As Range
Dim wsf As Object

Set wsf = WorksheetFunction

With Sheets("Sheet1")
  Set rngCheck = Union(.Range("A1:F1"), .Range("H1"))
  If wsf.CountBlank(.Range("A1:F1")) + wsf.CountBlank(.Range("H1")) < rngCheck.Cells.Count Then
    If wsf.CountA(.Range("A1:F1")) + wsf.CountA(.Range("H1")) < rngCheck.Cells.Count Then
      Cancel = True
      MsgBox "All cells on '" & .Name & "' in '" & rngCheck.Address(0, 0) & "' must be filled!", vbExclamation, "Abort Saving"
    End If
  End If
End With

Set rngCheck = Nothing
Set wsf = Nothing

End Sub

Ciao,
Holger

[loroverde]

Hello holger
How can i save this code with all the cells empty?
Because with all the cells empty i cant save the workbook