How to: if a range is not empty don't allow close?

[dan dungan]

Hi microsoft.public.excel.programming group,

I have a command button the copies cells to a text file.

Sometimes the data has not been saved to the text file.

I suspect the users have been closing the workbook before pressing the
command button.

So I tried to this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

_____________________________________
With Worksheets("CompleteQuote")
Dim rng As Range
Set rng = Range("test_range")
If rng.Value > 0 Then
Cancel = True
MsgBox ("You still have data that has not been exported. Please
cancel.")
Else: Cancel = False
End If
End With
End Sub
_____________________________________


This returns runtime error 13. Type Mismatch on
the following line:
If rng.Value > 0 Then

The range will contain some cells with data and some cells will have
no data.

Please offer any suggestions or questions.

Thanks,

Dan

 

[JLGWhiz]

Not sure what type data you have in the range, but if it is all numeric, then
this should do the trick:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

_____________________________________
With Worksheets("CompleteQuote")
Dim rng As Range
Set rng = Range("test_range")
If Application.WorksheetFunction.Sum(rng) > 0 Then
Cancel = True
MsgBox ("You still have data that has not been exported. Please
cancel.")
Else: Cancel = False
End If
End With
End Sub

 

[Barb Reinhardt]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range
With Worksheets("CompleteQuote")
Set rng = Nothing
On Error Resume Next
Set rng = .Range("test_range")
On Error GoTo 0
If Not rng Is Nothing Then
Cancel = True
MsgBox ("You still have data that has not been exported. Please
cancel.")
Else
Cancel = False
End If
End With
End Sub

 

[dan dungan]

Thanks JLG Whiz.

I'll look into this tomorrow.

Dan

 

[dan dungan]

Thanks Barb,

I'll look into this tomorrow.

Dan

 

[Barb Reinhardt]

I think I'd change the if statement to
If not isempty(rng) then

 

[dan dungan]

Hi Barbara,

I agree. The procedure as previously written returned the message box
message every time I tried to close the workbook.

Thanks again to both you and JLGWhiz.

Dan