Log In
Register
Home
June 16, 2019
Offline
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
Ttyagain:
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
October 5, 2010
Offline
Hi Martin,
Please supply the workbook so we can help.
Phil
Trusted Members
Moderators
November 1, 2018
Offline
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
June 16, 2019
Offline
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
June 16, 2019
Offline
HI Velouria & Philip
I have sent the attachment of my Excel file yesterday. Did you receive it ?
Kindly confirm
Thx
Martin Argimon
Trusted Members
Moderators
November 1, 2018
Offline
No, I see no attachment. Did you try the code I suggested? If so, what happened?
June 16, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
You didn't answer my question: did you actually try the code I suggested? If so, what happened? If not, why not? 😉
June 16, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
That is not the code I suggested. Please at least try what I posted.
June 16, 2019
Offline
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
1 Guest(s)
