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

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

--
Message posted via http://www.officekb.com

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


"Alan McQuaid via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

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

On Tue, 18 Aug 2009 11:24:01 -0700, Bob
wrote:

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


"Alan McQuaid via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com