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

  • Thread starter Thread starter dan dungan
  • Start date Start date
D

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
 
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
 
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
 
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
 
Back
Top