Tech Support Forum banner
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
146 Posts
Discussion Starter · #1 ·
I'm trying to code a timesheet so that employees cannot save/close the document with information not filled in. I kind of have it working where it won't save until the criteria is met, but the problem I find is if they close the workbook and select yes to save, it still saves and then closes. I would like it to either not save, or kick them back to the worksheet to make corrections.

Basically it just checks to see if the 3 fields in each row have any info. If not it's just a blank row. If it does, then they must have info in all 3 fields. It cycles through each worksheet and up to row 50 of each sheet. Here's what I have. Any ideas on how to fix the scenario when they close the workbook?

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

Dim Row As Integer
Dim I As Integer

For I = 1 To Worksheets.Count - 1

For Row = 5 To 50

If Worksheets(I).Cells(Row, 1) = "" And Worksheets(I).Cells(Row, 3) = "" And _
Worksheets(I).Cells(Row, 4) = "" Then

Else

If Worksheets(I).Cells(Row, 1) = "" Then
MsgBox ("Enter a Project Number on line " & Row & " of tab " & I)
Cancel = True
Exit Sub
End If

If Worksheets(I).Cells(Row, 3) = "" Then
MsgBox ("Enter an Activity Code on line " & Row & " of tab " & I)
Cancel = True
Exit Sub
End If

If Worksheets(I).Cells(Row, 4) = "" Then
MsgBox ("Enter an Activity Description on line " & Row & " of tab " & I)
Cancel = True
Exit Sub
End If

End If

Next Row

Next I

End Sub
 

·
Premium Member
Joined
·
39,538 Posts
Hi

Just a quick look - I'd suggest disabling Events - but you'll probably need to re-enable them after each Cancel = True line.
Code:
Application.EnableEvents = False
You might also want to copy your code to the Before_Close Event as well - that way you'll capture people trying to save or close before the relevant cells have been completed.
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top