CODEWITHSUNDEEP
excelerror-handlingrange
Emmanuel
Emmanuel

Reputation: 57

Excel Error Handling for range object

I'm working on an Excel user form where the user can input a range. For example, they can put in "B5" and "B20".

I'm trying to do error handling to prevent the user from putting in an incorrect range. For Example, "asdf" and "fdsa".

The following code fails:

Private Sub cmdSend_Click()
 Dim beginTerm As String
 Dim endTerm As String

 beginTerm = TermsBegin.Text
 endTerm = TermsEnd.Text

 If (IsError(Worksheets("Account Information").Range(beginTerm + ":" + endTerm)) = True) Then
     MsgBox "Cell Range is invalid."
     Exit Sub
 End If
 End Sub

I also tried the following:

Private Sub cmdSend_Click()
 Dim beginTerm As String
 Dim endTerm As String

 beginTerm = TermsBegin.Text
 endTerm = TermsEnd.Text
 Dim myRange As Range

 myRange = Worksheets("Account Information").Range(beginTerm + ":" + endTerm)
 On Error GoTo ErrHandler

 On Error GoTo 0

 'other code ...

 ErrHandler:
     MsgBox "Cell Range is invalid."
     Exit Sub
End Sub

My question is how can I handle the case that it fails?

Thanks!

Upvotes: 0

Views: 157

Answers (2)

Joseph
Joseph

Reputation: 5160

If you need to get a range from a user, I would recommend using Application.InputBox with a Type = 8. This allows the user to select a range from the worksheet.

Check this out: http://www.ozgrid.com/VBA/inputbox.htm

Also, if you are using a userform, you can also add a command button that will call the Application.InputBox to allow a user to select a range.

Quick example:

Private Sub CommandButton1_Click()
    Dim r As Range

    On Error Resume Next

    Set r = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
    If Not r Is Nothing Then MsgBox r.Address

    On Error GoTo 0
End Sub

Upvotes: 0

ZippyV
ZippyV

Reputation: 13018

You have to put

On Error GoTo ErrHandler

before the line that could throw the error.

Upvotes: 1

Related Questions