Excel - VBA - program to complete certain fields in a spreadsheet

minouiki 

Can't send the file. (voice of Alex T.) The answer is: "How VBA code can prevent a user from sending an Excel file (e.g., via email)"

(...Jeopardy music plays...)

Time's up. You wrote: "What is 'Trick question - It can't'?" Correct! Well, even if it could, that would be the behavior of a virus. Bonus points if you included "The VBA code might not even be executed."

Can't print the file. Is it possible for the user to make an entry in "the Special Cell" -- possibly a meaningless entry -- print the worksheet, and then remove that entry (or just close the file without saving it)? If your users are so unclever that they can't come up with that workaround, then yes, you can have hope. Using the VBA editor...

1. In the Project Explorer window, right-click on the ThisWorkbook code module*, and click View Code in the popup menu. (*It should be visible; if not, it's under the "Microsoft Excel Objects" pseudo-folder for your project.)
2. In the code window, from the left dropdown (the Object dropdown), select Workbook. If you have not previously entered code in this module, the editor creates an empty Workbook_Open event handler.

This procedure is not needed; you can ignore it (and optionally delete it after the next step).

3.

From the right dropdown (the Procedure dropdown), select BeforePrint. The editor creates an empty Workbook_BeforePrint event handler.

Notice that this procedure has a parameter named Cancel.  Notice also that the keyword ByVal does not precede Cancel, so code within this procedure can (always or conditionally) change its value to True.  (Well, it could change the value even if ByVal were present, but ByVal prevents any change to a native data type parameter from getting back to the calling procedure.)

Well, you need code to determine if the Special Cell has been filled in properly. Something like...

    Dim objSheet    As Worksheet
    Dim objSpecialCell  As Range
    Dim strCellContent  As String
    
    '----   Grab the value in Lizzz2395's Special Cell.
    Set objSheet = Sheets("Sheet1")
    Set objSpecialCell = objSheet.Range("C4")
    strCellContent = objSpecialCell.Value
    
    '----   See if the value meets the requirement.  Note that the
    '       code should not just check for an empty string, because
    '       then one or more spaces would pass the requirement.
    '       (And remember than unlike Excel comparisons of strings,
    '       VBA comparisons using the = and <> operators are case-
    '       sensitive.)
    If Trim$(strCellContent) = "" Then
        Cancel = True
        Call MsgBox("You cannot print this workbook until you" _
                & " enter data in the Special Cell." _
                , vbInformation Or vbOKOnly)
    End If

As should be obvious, the code becomes more complicated if the address of the Special Cell has to be determined at runtime.

But there is a weakness here. Can you spot it? And it's in addition to the fact that a smarter user could view and modify (or prevent execution of) this procedure ... which you could sort-of prevent by password-protecting the VBA project; how-to (but that can be defeated).  Beginning to sound futile, isn't it?

Can't save the file. This is about the same as preventing the file from being printed, but the user has to be slightly more clever to work around the protection. The event handler for this is Workbook_BeforeSave.