Do not allow close 1

[cjjn]

Hi there

Is there a way to code in VBA for Excel - that if a particular cell is empty - it wont allow close?

My help files in VBA are not installed and thought I may get an answer quicker this wat than waiting for my service desk to re-install them.

Thanks!!

 

[duGly]

Try this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If [xlRequiredCell] = "" Then
        MsgBox "Please enter a value in [location].", _
            vbOKOnly + vbExclamation, "Input required"
        [xlRequiredCell].Select
        Cancel = True
    End If

End Sub

This procedure must be placed in the ThisWorkbook module. It fires when your user attempts to close the workbook. Setting Cancel=True cancels the close event.

This assumes that you have named a cell "xlRequiredCell". You could also just refer to it by an address like this: Range("B3"). I prefer naming cells, however, as this makes programming much simpler.

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]

 

[cjjn]

Thank you so much for your help - and quick response.
Worked like a charm!

Thanks again - have a fantastic day!