Requiring multiple cells to be populated before allowing Save

[Bob]

I found the following code snippet that will not allow a user to save a
worksheet until cell M2 has been populated:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Sheets("Sheet1").Range("M2").Value) Then
Cancel = True
MsgBox ("The workbook cannot be saved until cell M2 has been
populated.")
End If
End Sub

Being relatively new to VBA, can someone tell me how to modify the code
above so that a range (M2:M25) must be populated before a user is allowed to
save the worksheet?

I tried changing the 2nd code line above to:

If IsEmpty(Sheets("Sheet1").Range("M2:M25").Value) Then

but that didn't work. Any help would be greatly appreciated.

Thanks,
Bob

 

[Alan McQuaid via OfficeKB.com]

Hi Bob,

You could try:

For Each iCell In Sheets("Sheet1").Range("M2:M25")
If IsEmpty(Sheets("Sheet1").Range(iCell.Address)) Then
Cancel = True
MsgBox ("The workbook cannot be saved until cell " & iCell.Address & " has
been populated.")
Exit Sub
End If
Next iCell


Alan

 

[Bob]

Hi Alan,

Thanks for your solution. I assume I should precede your code with:

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

Is that true?

Thanks again for your help!
Bob

 

[Gord Dibben]

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

Alan's code here

End Sub

Entered in Thisworkbook module, not a sheet or general module.


Gord Dibben MS Excel MVP