CODEWITHSUNDEEP
excelvbaerror-handling
Heather Won
Heather Won

Reputation: 23

Error handling with loop and user input

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.

Upvotes: 0

Views: 75

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27269

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

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6852

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).

Upvotes: 0

Related Questions