Excel Error Handling for range object

Question

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!

Maybe just use a Refedit control instead?

Tim Williams
– Tim Williams

2013-02-06 15:57:52 +00:00
Commented Feb 6, 2013 at 15:57 — Tim Williams
– Tim Williams, Commented Feb 6, 2013 at 15:57

ZippyV · Accepted Answer · 2013-02-06 15:56:17Z

1

You have to put

On Error GoTo ErrHandler

before the line that could throw the error.

answered Feb 6, 2013 at 15:56

ZippyV

13.1k3 gold badges39 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joseph · Accepted Answer · 2013-02-06 16:00:59Z

0

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

answered Feb 6, 2013 at 16:00

Joseph

5,2001 gold badge27 silver badges26 bronze badges

Collectives™ on Stack Overflow

Excel Error Handling for range object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related