Error handling with loop and user input

Question

For i = 1 To repNumber
    TryAgain:
    On Error GoTo ErrH:

    repName = InputBox("Enter rep name you want to exclude.", "Name of Rep")
    .PivotItems(repName).Visible = False
Next i

ErrH:
        MsgBox "Try Again"
        GoTo TryAgain:

It shows an error if I type in Rep name that doesn't exist in the PivotTable. So I'm trying to use an error handler to let a user type Rep name again. But after the second time I type something wrong, instead of going to the error handler, the code terminates itself.

I'm not sure if 'On Error GoTo' is in the wrong line.

Scott Holtzman · Accepted Answer · 2015-10-22 19:55:18Z

1

You can avoid Error Handing and GoTo statements all together (which is definitely best practice) by testing within the code itself and using If blocks and Do loops (et. al.).

See this code which should accomplish the same thing:

Dim pf As PivotField, pi As PivotItem
Set pf = PivotTables(1).PivotField("myField") 'adjust to your needs

For i = 1 To repNumber

    Do

        Dim bFound As Boolean
        bFound = False

        repName = InputBox("Enter rep name you want to exclude.", "Name of Rep")

        For Each pi In pf.PivotItems

            If pi.Value = repName Then
                pi.Visible = False
                bFound = True
                Exit For
            End If

        Next pi

    Loop Until bFound = True

Next i

answered Oct 22, 2015 at 19:55

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

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

Comments

KekuSemau · Accepted Answer · 2015-10-22 19:45:20Z

0

Try Resume TryAgain instead of GoTo TryAgain.

(You don't need : in these statements, it is by coincidence allowed because it is also used to seperate multiple statements within a line, so it is just meaningless here).

answered Oct 22, 2015 at 19:45

KekuSemau

6,8434 gold badges27 silver badges37 bronze badges

Collectives™ on Stack Overflow

Error handling with loop and user input

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