Before_Close macro, How to...?
 

I am for sure missing some fundamental concept on how to make a Before_Close macro work.

I have tried several combinations of examples from google searches and none will check A1 to A200 for contents, I close the workbook, and it just closes with no action taken.

These are both in the ThisWorkbook module.

I can run the non-event code from the sheet "ABC" and it works just as I need.

I've tried calling the non-event from the Before_Close and it too does not respond.

Thanks,
Howard

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then

If WorksheetFunction.CountA(Sheets("ABC").Range("A1:A 200")) < 200 Then

Dim Reply As VbMsgBoxResult
Reply = MsgBox("Cells A1 to A200" _
& vbCr & "must have values" _
& vbCr & "before the workbook is closed", _
vbOKOnly + vbExclamation, "Two Hundred Empty!")

Else
End If
End If
'Cancel = True
End Sub


Sub MyCheckOnColumnA()

If WorksheetFunction.CountA(Sheets("ABC").Range("A1:A 200")) < 200 Then
Dim Reply As VbMsgBoxResult
Reply = MsgBox("Cells A1 to A200" _
& vbCr & "must have values" _
& vbCr & "before the workbook is closed", _
vbOKOnly + vbExclamation, "Two Hundred Empty!")

Else
End If

End Sub

Before_Close macro, How to...?
 

Hi Howard,

Am Mon, 25 May 2015 00:52:50 -0700 (PDT) schrieb L. Howard:

I am for sure missing some fundamental concept on how to make a Before_Close macro work.

I have tried several combinations of examples from google searches and none will check A1 to A200 for contents, I close the workbook, and it just closes with no action taken.

try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myCnt As Long, Diff As Long

myCnt = Application.CountA(Sheets("ABC").Range("A1:A200"))
Diff = 200 - myCnt
If Not Me.Saved Then
If Diff 0 Then

MsgBox "Cells A1 to A200" _
& vbCr & "must have values" _
& vbCr & "before the workbook is closed" _
& vbCr & Diff & " cells are empty!"

Cancel = True
End If
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Before_Close macro, How to...?
 

Hi Howard,

Am Mon, 25 May 2015 10:18:39 +0200 schrieb Claus Busch:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

you better put the code into Workbook_BeforeSave event.
If someone saves the workbook with less than 200 values in column A and
the workbook is saved your code will not be fired.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Before_Close macro, How to...?
 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

you better put the code into Workbook_BeforeSave event.
If someone saves the workbook with less than 200 values in column A and
the workbook is saved your code will not be fired.


That works fine in the Workbook_BeforeSave.

However, I was getting the same results as before with the first attempts t0 test your code. So I decided to close Excel completely and reopen.

Problem solved, so now I am wondering if some of my prior efforts were good code, but Excel was messing with me. Hard to say, but this is a go.

Many thanks, Claus.

Howard