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



"dan dungan" wrote:

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

Thanks JLG Whiz.

I'll look into this tomorrow.

Dan

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


--
HTH,
Barb Reinhardt



"dan dungan" wrote:

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

Thanks Barb,

I'll look into this tomorrow.

Dan

I think I'd change the if statement to
If not isempty(rng) then
--
HTH,
Barb Reinhardt



"dan dungan" wrote:

Thanks Barb,

I'll look into this tomorrow.

Dan

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