WHY OH WHY DOESN'T THIS WORK!!!!!

[WhytheQ]

I have this bit of code:


Public InvoiceRange As Range

Sub SelectDataRow()
'finds the last used row in the sheet that the user presently
has active
Dim LastUsedRow As Integer
LastUsedRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row


'get the user to select a row of data for moving to the Log
Set InvoiceRange = Application.InputBox("Either: " & vbCrLf & _
"1.Confirm the present selection by
hitting OK" & vbCrLf & _
"2.Select a cell in another row and hit
OK" & vbCrLf & _
"3.Hit CANCEL to stop the macro here", _
"RELEVANT PAYMENT INFORMATION", "A" & _
LastUsedRow & ":L" & LastUsedRow, , , , ,
8)


'if nothing has been selected on the activesheet then this is
flagged to the user
If InvoiceRange Is Nothing Then
MsgBox "No cells on the ACTIVESHEET have been selected"
End If

End Sub


It doesn't seem to always work!! When it hits the "Set InvoiceRange
=..." line it sometimes works, and sometimes fails. The error is Object
Required.
I realise that if nothing is selected when the Inputbox method is used
then this is the correct error but even when I select a cell or range
on the activesheet the error can occur.

SOMEBODY PLEASE HELP AS I'M REALLY BANGING MY HEAD AGAINST A BRICK WALL
WITH THIS ONE!!!

Any help greatly appreciated,
Jason.

 

[Die_Another_Day]

Use On Error Resume Next Before the Set command. When you Click Cancel,
then the Input Box returns false, which is not an object. Then your
check to see if InputRange is nothing should work correctly

Charles Chickering

 

[Dave Peterson]

Just to add an example:

Dim InvoiceRange as range
....

set InvoiceRange = nothing
on error resume next
set invoicerange = application.inputbox(Prompt:="long string", type:=8)
on error goto 0

if invoicerange is nothing then
'cancel
else
'not cancel
end if

 

[WhytheQ]

Thanks very much lads.

Regards
Jason.

Just to add an example:

Dim InvoiceRange as range
...

set InvoiceRange = nothing
on error resume next
set invoicerange = application.inputbox(Prompt:="long string", type:=8)
on error goto 0

if invoicerange is nothing then
'cancel
else
'not cancel
end if