Hi Martin,

Please supply the workbook so we can help.

Phil

Filtering for a value that isn't there will not cause an error, you just get no data shown. You can test for that by checking there is more than one visible cell in a column of the filtered data (the header cell should always be visible):

Private Sub CmdbuttonPurchaseSummary_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Purchase_Summary")
Dim ordernumber As Integer

ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)

dsh.Range("C21:R400").ClearContents

sh.AutoFilterMode = False
With sh.Range("Beneficiaries_Burials")
.AutoFilter Field:=1, Criteria1:=ordernumber
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
dsh.Range("C11").Value = ordernumber
.Copy
dsh.Range("C21").PasteSpecial xlPasteValues
Else
MsgBox "you have entered the wrong Order Number"
End If
End With
sh.AutoFilterMode = False
sh.ShowAllData

End Sub

HI Philip

Herewith my excel sheet attached.

Briefly this Program is for the Purchasing of Parcels / Graves in a Memorial Park. The products ( see Products Sheet) , are usually a single parcel or a "family garden or estate, etc. It means that when the buyer purchases a Product for his family ( in the case of a Daily of 6 individuals ) , these persons are listed under the Beneficiaries_Burials Table.

I do not want to overload you with info, but if you need further clarification , please let me know

PROBLEM STATEMENT:

If you go to the 'Purchase_Summary' sheet, you will find the Code saved under this sheet. You can click the "Enter Order Number" button ( top right corner) to select the order number to get all the info related to aN specific order . It ONLY works if i enter exactly any of the order numbers listed in the "Orders" sheet. If i enter any other number which is NOT in the orders sheet it copies "ALL" the records that are extracted from the "Beneficiaries_Burials" sheet, although i tried to include the 'On ERROR' statement as well as the "if Nothing" statement to avoid this.

NOT SURE how to get the code to pause or retry, if i enter a wrong order number or even the cancel button.

Thanking you in advance

HI Velouria & Philip

I have sent the attachment of my Excel file yesterday. Did you receive it ?

Kindly confirm

Thx

Martin Argimon 

No, I see no attachment. Did you try the code I suggested? If so, what happened?

HI Velouria

I've tried uploading my excel file , but is rejecting it since it exceeds the size limit. My file is 1,4 MB

I wish you could see the entire program is actually a relational database with various tables and interfaces.

I do understand your suggestion, but the problem is that if do not enter an 'existing ' order number the auto filter pushes a lot of data in to my query which is not what i want.

Is difficult for me to explain the entire problem, I wish i could send you the file , which is very easy to understand.

Any other ways that I can attach a larger file ?

Kindly let me know

Many Thanks

Kind regards

Martin Argimon 

You didn't answer my question: did you actually try the code I suggested? If so, what happened? If not, why not? 😉

Velouria.

I just to summarise my question. I just want the "On error" statement to work within my macro at the moment if i enter the wrong order number does not stop and does not ask me to retry.

Kindly check my code below and let me know where i went wrong?

Many Thanks

Martin Argimon ( code below)

Private Sub CmdbuttonPurchaseSummary_Click()

    Dim sh As Worksheet

    Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")

    Dim dsh As Worksheet

    Set dsh = ThisWorkbook.Sheets("Purchase_Summary")

    Dim ordernumber As Integer

'TryAgain:

ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)

dsh.Range("C21:R400").ClearContents

sh.Activate

sh.AutoFilterMode = False

'On Error GoTo TryAgain

sh.Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=ordernumber

'On Error GoTo 0

sh.Range("Beneficiaries_Burials").Copy

dsh.Activate

dsh.Range("C11").Value = ordernumber

dsh.Range("C21").PasteSpecial xlPasteValues

sh.AutoFilterMode = False

sh.ShowAllData

'Exit Sub

'TryAgain:

'   MsgBox "you have entered the wrong Order Number"

End Sub

That is not the code I suggested. Please at least try what I posted.

Sorry

Your suggestion is NOT applicable

You insist on telling me something that is NOT my problem

I told you that is the 'On Error" what does not work

You do not need to be so Proud !!!

regards

Martin Argimon