Validation Prior to Send As Attachment

[EliseFreedman]

Hi There

I have been tasked with building a balanced scorecard application using Excel. The idea is that individual scorecards can be emailed out to different departments. The department completes the scorecard and emails it back to us. The problem I have got is how do I ensure that ALL the fields have been completed before the file is sent back? I am using the code below to check the data input when the file is saved. This works fine. However, it is fairly simple to Go to the File Menu and select "Send File as attachment" without actually saving the file therefore bypassing the validation routine. I am aware that I could attach the code to a command button which validates the input and then emails the sheet. The trouble I have got is that due to security issues in our Network not all users are able to run macros and thefore have to resort to using "File, Send To". Does anyone have any ideas how I can get round this and validate the data before the "File, Send To" is performed?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA(Sheet1.Range("L16:L28")) < 13 Then
        MsgBox "Please enter a grading in all cells", vbCritical
        Cancel = True
        Exit Sub
    End If
If WorksheetFunction.CountA(Sheet1.Range("G5")) < 1 Then
        MsgBox "Please enter a department name", vbCritical
        Cancel = True
        Exit Sub
    End If
End Sub

 

[SkipVought]

Hi,

Since "not all users are able to run macros and thefore have to resort to " a NON VBA solution, this really belongs in forum68.

Having said that, try using a Conditional Format, maybe something like [highlight red][white]RED[/white][/highlight] interior, in empty cells. It will not PREVENT the user from sending, but it will be a, well, er, uh [highlight red][white]RED FLAG![/white][/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}