Cant get vba code to work as desired... [Closed]

Report
-
Hi,

Looking for a solution to the following issue with some vba code I was kindly given.

1) I have a scenario where if a user leaves one or more cells blank within specified cell ranges in a worksheet, then tries to close the worksheet, an error message pops up telling them all cells must be populated. The user cannot close the file unless this is done.

2) When all the cells in the same ranges specified are not blank, then the file can be closed normally and the error message wont appear.


I can get the code to execute for part 1) but not part 2). Here's the code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Zell As Range
Dim flag As Boolean
flag = False
If Cells(1, 1) = "" Then flag = True
For Each Zell In Range("C9:C14")
If Zell = "" Then
flag = True
Exit For
End If
Next Zell
For Each Zell In Range("C18:C25")
If Zell = "" Then
flag = True
Exit For
End If
Next Zell
For Each Zell In Range("C30:E35")
If Zell = "" Then
flag = True
Exit For
End If
Next Zell

Cancel = flag
If flag Then MsgBox "Please ensure all cells are complete before the file is closed"
End Sub


Can anyone help with this? Many thanks in advance, Cal.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!