On error, go back a step in macro

Hi,

I have a macro in one file which uses an input box where the user types in a subcontractor code to retrieve address and contact details from another file.

If the user inputs the incorrect subcontractor code, an error message box pops up. Currently the user then has to select a button in the original file to restart the macro at the input box stage.

What I want to happen is when ok is selected on the error message, to return to the input box so in my macro below where the error message says "An error occurred - you asked for a subcontractor that does not exist. Please try again." and the user selects OK, I would like it to return to the 'Enter the Subcontractor line and the "Please enter the Subcontractor code" input box reappears.

Have searched everywhere but with my limited skills still not resolved.

Any ideas?

Sub Supplier()
    
'obtain subcontractor details
    
    Dim ordSubcontractor
    Dim Subcontractor
    
    'Enter the Subcontractor
    Subcontractor = InputBox( _
        prompt:="Please enter the Subcontractor Code.")
    
    'goto subcontractor and address workbook and find details
    Application.ScreenUpdating = False
    On Error GoTo AddressError
    Windows("ORDADDSS.XLS").Activate
    ordSubcontractor = Range(Subcontractor)
     
    'write address to order
    Application.ScreenUpdating = True
    Windows("SUBCONTRACT ORDER.XLS").Activate
    Range("ordsubcontractor") = ordSubcontractor

GoTo endsub

    'if error occurs finding address file or references
AddressError:
    Application.ScreenUpdating = True
    Windows("SUBCONTRACT ORDER.XLS").Activate
    MsgBox "An error occured - you asked for a subcontractor " & _
            "that does not exist. Please try again."
   
endsub:

End Sub

Hi - your control flow is doing this by sending the code to the end of the routine ... without changing your code too much you could do something like this (my bits in red):

Sub Supplier()
    
'obtain subcontractor details
    
    Dim ordSubcontractor
    Dim Subcontractor

InputCode:    
    'Enter the Subcontractor
    Subcontractor = InputBox( _
        prompt:="Please enter the Subcontractor Code.")
    
    'goto subcontractor and address workbook and find details
    Application.ScreenUpdating = False
    On Error GoTo AddressError
    Windows("ORDADDSS.XLS").Activate
    ordSubcontractor = Range(Subcontractor)
     
    'write address to order
    Application.ScreenUpdating = True
    Windows("SUBCONTRACT ORDER.XLS").Activate
    Range("ordsubcontractor") = ordSubcontractor

    GoTo endsub

    'if error occurs finding address file or references
AddressError:
    Application.ScreenUpdating = True
    Windows("SUBCONTRACT ORDER.XLS").Activate
    MsgBox "An error occured - you asked for a subcontractor " & _
            "that does not exist. Please try again."
   GoTo InputCode

endsub:

End Sub

You don't declare your variables as a specific type, so Excel will always interpret them as variants which can slow code down.

What is the supplier code because you use it as Range(SubContractor)?

You don't need to activate the other workbook.

I would prefer to give the user an option to quit or try again.

It's not good code practice to rely on generating an error.

Try this code

Option Explicit

Sub Supplier()

'obtain subcontractor details
    Dim ordSubcontractor As String
    Dim Subcontractor As String

    'Enter the Subcontractor
    Subcontractor = InputBox( _
                    prompt:="Please enter the Subcontractor Code.")
    'goto subcontractor and address workbook and find details
    On Error GoTo AddressError
    ordSubcontractor = Workbooks("ORDADDSS").Range(Subcontractor).Value
    'write address to order
    Workbooks("SUBCONTRACT ORDERS").Range("ordsubcontractor") = ordSubcontractor
    Exit Sub
    'if error occurs finding address file or references
AddressError:

    Select Case MsgBox("An error occured - you asked for a subcontractor " & _
                       vbCrLf & "that does not exist. Do you want to try again.""" _
                       , vbOKCancel Or vbCritical Or vbDefaultButton1, "Incorrect subcontractor")

        Case vbOK
            Supplier
        Case vbCancel
            Exit Sub
    End Select

End Sub

I can't try the code without an example workbook(s)

I think you would be better using the Find method to find the entry, not rely on Named Ranges.

Your contractor addresses workbook is designed such that it will create extra work - adding Named Ranges, etc. Much better to use a classic Table layout so that you can use the .Find method. Also, why not keep the addresses in the PO template workbook then you could use Data Validation & VLOOKUP without VBA.

Post back when you have recreated the address book.