Error when Inputbox is Canceled

[xxthegiantxx]

I have an input box set up to prompt the user to select a range of cells. When i select cancel and no data range was selected, i get a run-time error '13: type mismatch. Clicking on debug it refers me to my set calculated_range line of code. Not sure what i am doing wrong. The code works perfectly when a range is selected.
Also, if you have link to a video or website that you would recommend for learning vba i would appreciate it.

Here's my code:

 Sub Button3_Click()

Dim Calculated_Range
Set Calculated_Range = Application.InputBox("Calculate Weekly Hours", "Select a Range from Column 'F'", "Enter Range", , , , , 8)


If Calculated_Range = "" Then
MsgBox "No Range Selected"
GoTo ending
Else
MsgBox "Range Selected: " & Calculated_Range.Address
End If




Range("R3").Formula = "=Sum(" & Calculated_Range.Address & ")"






ending:


End Sub

 

[mikerickson]

When you cancel an Application.InputBox it returns False. Which is not a Range object, hense the mismatch

Try

On Error Resume Next
Set Calculated_Range = Application.InputBox("Calculate Weekly Hours", "Select a Range from Column 'F'", "Enter Range", , , , , 8)
On Error Goto 0

If Calculated_Range Is Nothing Then
    MsgBox "No Range Selected"
    GoTo ending
Else
    MsgBox "Range Selected: " & Calculated_Range.Address
End If

 

[Fluff]

For links to useful sites, have a look at the list that Hiker95 has put together
https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

 

[xxthegiantxx]

this comment was edited to quote the commentor

 

[xxthegiantxx]

When you cancel an Application.InputBox it returns False. Which is not a Range object, hense the mismatch

Try

On Error Resume Next
Set Calculated_Range = Application.InputBox("Calculate Weekly Hours", "Select a Range from Column 'F'", "Enter Range", , , , , 8)
On Error Goto 0

If Calculated_Range Is Nothing Then
    MsgBox "No Range Selected"
    GoTo ending
Else
    MsgBox "Range Selected: " & Calculated_Range.Address
End If

This results in an object error on If Calculated_Range Is Nothing Then. However, i changed "Is Nothing" to = "" and it worked. Thanks for your help.

 

[xxthegiantxx]

so changing the is nothing then part of the code to = "" then allows the message box to work for a cancel selection on the input box but then gives a type mismatch error for the range calculation. Switching it back to is nothing then, allows the range calculation to work but again gives me an error when cancel is selected. Something is still missing.

 

[RoryA]

You should have this declaration in your code:

Dim Calculated_Range As Range

 

[xxthegiantxx]

You should have this declaration in your code:

Dim Calculated_Range As Range

Thanks Rory this helped alot. In addition i moved the code that puts the selected range into R3 to before the end of the if statement. All works good now. Thanks all for your help on this. Here's the code in case someone else runs into a similar problem:

Sub Button3_Click()


Dim Calculated_Range As Range
On Error Resume Next
Set Calculated_Range = Application.InputBox("Calculate Weekly Hours", "Select a Range from Column 'F'", "Enter Range", , , , , 8)
On Error GoTo 0


If Calculated_Range Is Nothing Then
MsgBox "No Range Selected"
Else
MsgBox "Range Selected: " & Calculated_Range.Address
Range("R3").Formula = "=Sum(" & Calculated_Range.Address & ")"
End If






End Sub