Call back an order and modify or delete it !

[apla]

Good morning all!

I use this code for transfer the data of an order in another sheet and create a data base for these orders..This works fine and no problem for this.I just post my code..here What i need is this:

What I need is to call back an order and to be able:
1) Just look it OR
2) Modify something in the order using a Msbox asking:"Are you sure?" OR
3) Delete this order(asking from a Msbox if iam sure or not.
In this case I need the results sheet to be sorted too.

Thanks in advance for any suggestion.

Please Login or Register  to view this content.

[apla]

Any idea for this, pls?

[AB33]

Apla,
Could you explain what do you mean by OR.

Please Login or Register  to view this content.

You have the first line which gives you two option, yes, or no, if it is yes, the code goes in to next line and if it is no, the code exit the sub.
What do you want to change or add to the code? What are you trying to do?

[apla]

Hi and thank you for trying to help me.

The code need no change at all. Works perfectly. I just mentioned that i use this code to get my results in "Result" sheet.

What i need is to call back an order.

Let's say that i need to call back(see again) the order number 6 from result sheet.

When i'll see this order i'd like to have these options.

Option 1.

Just look the order

Option 2.

Modify and save something in the order using a Msbox asking:"Are you sure?"

Option 3

Delete this order(asking from a Msbox if iam sure or not.

Hope that is clear now.

[AB33]

Apla,
You need to help me to understand what do you mean by call back an order?
There are 3 modules in the attached sample.
Sub Macro2()
sorts the data in sheet order if the answer to the input box question is yes
It then copies some column from sheet order in to sheet result.
It then clears the copied column from sheet order.
Final the code goes to this line

If MsgBox("Do you want to print?", vbYesNo) = vbYes Then.

There is no a lot on the code except for few copying range from sheet order to sheet result, but I do not understand where is call back an order?
Please show me your desired result in a separate out as this is easy than explaining in words.
The other two codes you have do copy from one sheet to another.

[apla]

Apologize for the delay in my reply. I was on a business trip for few days, away from my office. Thank you for trying to help me.

Let's try another way. Forget at all the code(s) that exist in my workbook. We have to do nothing with these.

I believe that in my new example sheet is clear at all.

Let's say that i want to see in orders sheet, an previous order that exist in Results sheet.

So first we must choosing the number, to see the order as it was.

Then I need a macro that clicking the button " Delete"
To do these:
1)A Msg box to ask:"Are you sure"
If answer is no, then do nothing. I f answer is YES then:
1)Clear ranges B33:B35 and d33:g57
2) Delete in Results sheet the range A25:O26

Again pls see the example sheet.

[AB33]

apla,
I am not sure if this what you want. I have added few lines as per your request. I have added another msg box, but not sure if you need it.
Then clearing the range.
Finally sorting the range.
You keep mentioning an order no. Yes, I can see an order no 42,but it does not have a drop down arrow, or is not linked to any formula or macro. I do not see anywhere which refers to C30.

[apla]

Hi there

The order number in C30 will be puted manually. That's why you don't see any validation list there.

So when we type an order number in c30, first of all we have to see the order in our ranges
B33:B38 AND D33:G56.

Then we have to delete it(if we want to..)

In your example sheet

I see that order 42 is deleted in Results sheet.

But in this sheet I still can see the numbers in
range B36:B38. These also should be deleted

After this when I put in C30, the order number 6,
this order(i have highlighted this order in Result sheet) should be appears in ranges
B33:B38 AND D33:G56.

Then clicking the delete button, a Msbox should appears asking if i want to delete this or not and if i click yes then ranges B33:B38 AND D33:G56, should be cleared and the highlighted order number 6 in Results sheet should be deleted.

I have added few lines as per your request.

Not able to find these few lines!

[AB33]

Apla,
Good!
Despite, I still do not understand your request, it is good that we are taking step by step and eventually, I should be able to resolve it. There is nothing complication in your request, just need to understand it.
So, you want to clear cells from sheet result not order, it was my mistake to do it from order sheet
Do all these should apply to sheet result or order sheet?

Msg box to ask:"Are you sure"
If answer is no, then do nothing. I f answer is YES then:
1)Clear ranges B33:B35 and d33:g57
2) Delete in Results sheet the range A25:O26

"So when we type an order number in c30, first of all we have to see the order in our ranges
B33:B38 AND D33:G56."
My reply:
But these lines are empty in sheet order. How do you fill in these range, or where do you get the figures from?

"After this when I put in C30, the order number 6,
this order(i have highlighted this order in Result sheet) should be appears in ranges
B33:B38 AND D33:G56."

My reply:
Yes, I see rows 2-4 are highlighted in sheet result(Order no 6), so should be cleared as well? Which one should appear
B33:B38 AND D33:G56."

I do not mind writing the code from a scratch if I understand your request.

[AB33]

I have added comments to the main part of the code, so as to understand which line does what and it also help me to add/delete rows.

Please Login or Register  to view this content.

[apla]

First of all i have to say that your try to help me i too much appreciated!

My goal is this.

We have many orders per day that using the order sheet as a user form(user type on this sheet), all these orders are saved in result sheet.

Now for any reason we want to delete an order from the database(Results sheet). Ok.

So as the user will not have ant access in result sheet, the idea is using another range in order sheet(B33:B35 and d33:g57),to call back any order we nned using C30 cell. You asked me for this.

..But these lines are empty in sheet order. How do you fill in these range, or where do you get the figures from?

The answer is that i don't fill these ranges. This is the first thing that i ask for the new macro to do. Fill these ranges using the data that exist in results sheet using the order number in C30.

..Yes, I see rows 2-4 are highlighted in sheet result(Order no 6), so should be cleared as well? Which one should appear
B33:B38 AND D33:G56."

Yes these should be cleared as well.

Is this another good step?

[AB33]

Yes, I see rows 2-4 are highlighted in sheet result(Order no 6), so should be cleared as well? Which one should appear
B33:B38 AND D33:G56."
My reply:
It is because order no 6 appears in C30, so we have to find these order no 6 in sheet result.
It is not simply finding it in rows 2-4. What if it is order no 10 which is found in rows 9-10? So, the order no could be anywhere in sheet result and need to find it first before we clear it. Is this right? If it is, there is no code which does that, so I need to add another few lines to do it. OR it is always in the first lines, so clear rows 1-3?

[AB33]

The answer is that i don't fill these ranges. This is the first thing that i ask for the new macro to do. Fill these ranges using the data that exist in results sheet using the order number in C30.

Could you please the code for this task? I need to see how the range in sheet order are filled in.

[apla]

...The answer is that i don't fill these ranges. This is the first thing that i ask for the new macro to do. Fill these ranges using the data that exist in results sheet using the order number in C30.

Could you please the code for this task? I need to see how the range in sheet order are filled in.

I try to tell you that i don't have this code. This is the first code that i need from you.

In my example sheet in post#6 i show how the range in sheet order is filled in using the example of order 42.

.What if it is order no 10 which is found in rows 9-10? So, the order no could be anywhere in sheet result and need to find it first before we clear it. Is this right?

Corect 100%.

...OR it is always in the first lines, so clear rows 1-3?

No it can be anywhere in sheet results.

[AB33]

I try to tell you that i don't have this code. This is the first code that i need from you.
No problem!
I can write you could to do that, but explain to or show me where do we start?
So far, and in post 6, you are referring to clearing range, but not filling them.

I see you have 3 codes in the attached, but none of them refer to copying or filling in to sheer order, so where do I start to fill in sheet orders from A-G57?

[apla]

..I can write you could to do that, but explain to or show me where do we start?

I upload for you a new sample sheet without codes at all and without any data in Range A1:O29. So we have these worksheets.

I show there what i want to see when i fill C30 with an Order number. In this case the order is number 42.

Then and after that we see all these there we need another code for clear this range and delete the order in result sheet.

Is it more clear now?

[AB33]

Please check the attached. There are two codes at the bottom of module 11. This is just a start.
The first code clears the rows from sheet result depending on the value of C30 of sheet order.
The second code fills in sheet order from sheet result.
If you have more than 2 code 42, which one do you choose to fill in B33-B38?
There is no problem with column D as we can fill them in the next row.
Once we resolve the issue, what is the next?
Please do not delete any code from the attached sheet, just show me on the sheets what needs to be done.

[apla]

.

.Please do not delete any code from the attached sheet, just show me on the sheets what needs to be done.

My beleiving was that existings codes were more comfused you rather than helped you. Just why i deleted them.
I'll keep in mind for the next time.

..There is no problem with column D as we can fill them in the next row.

Not sure what you mean by that!

..This is just a start.

I believe that is also the end of this thread. Both of your codes work fine. I don't see to be any problem in my sample sheet. I'll try tomorrow morning in the real workbook and if there is any issue i'll let you know.

...If you have more than 2 code 42, which one do you choose to fill in B33-B38?

In my real book numbers of the order will be uniques!

I added some more lines in your code to clear the ranges in sheet orders and sort the Results sheet.

Please Login or Register  to view this content.

..Once we resolve the issue, what is the next?

Not sure what do you mean by that!

I would like to thank you very much for your efforts and of course for your excellent solution.

[AB33]

Apla,
I appears that you have given up before I do.
I have not tied the codes up yet. It was just for illustration only- to see if my understanding is correct.
When the code searches for order no in sheet result, there are more than order nos found. For e.g row 25 and 26, so which one do you copy in to sheet order?
If you look at order no 6, there are 3 rows, which of the 3 rows is copied in to sheet order?
You have not included the code which searches the order no and copy the rows in to sheet order, so I am still at loss how you are going to fill in sheet order if you want to re-run the code.

[apla]

Hi there!

...I appears that you have given up before I do.

This simply can not ever be done!

You are talking to a Greek man who was borned in Sparta.

Have you ever read about Battle of Thermopylae? There the Spartan king Leonidas with 300 fighters, said the famous all over the world "come and take them"

I never give up!!

Now to our issue.

Apologize for the delay but too much work...

Your codes as i said work fine, but as you also said must be a start..

Both of your codes do exactly what i want but the biggest problem is this.

In my first example sheet as you perhaps noticed in B3 thers is a validation list to choose customer. When we choose the customer using the code that exists in Module of sheet Orders, column D(D2:D25) is filled with all the products that this special customer use.

After i run your codes, this option stops to works. Choose customer in B3 but nothing appears in D2:D25.

Is this that you were talking about?

[AB33]

No,
the code looks at

Please Login or Register  to view this content.

Which is the order no and find it in sheet result (column D), and then copy the columns and paste in B33 and D33.

[apla]

Sorry but now it's me the one that i don't understand.

[AB33]

Please read post # 11.
I asked you how do you fill-in the data in sheet order. I thought- and had worked on these assumption. What is the link between C30 and order no in sheet result column D?
It appears, you have changed the layout of sheet order. See again the sheet you had attached. It clearly shows that Range B33 are filled in from sheet order by matching C30 of sheet order with sheet result of column D. I might have been working on the wrong assumption from the start.

[apla]

I readed carefully post#11. No difference to what i say now.

I didn't change the layout of the sheet. Is the same as my post#6.

... See again the sheet you had attached. It clearly shows that Range B33 are filled in from sheet order by matching C30 of sheet order with sheet result of column D

B33 is manually filled in that example-post#6) just to show you what i want to see.

C30 is the cell that we type the order number. Then your code looks-correctly- for matcing in D column Of Results sheet and fill the range with the correct results.

Again i say. Both your codes work fine. The only problem is that after using these does not works the code that i have in module of Order sheet.

Of course i can understand that you are tired with this issue, but believe me i do my best to clear everything you need.

[AB33]

In post #20, you are referring to data validation in B3, but the code I have attached in post #17, does not touch B3. I did not see any data validation in B33. The code simply filled in Range B33 from sheet result.
If the code I have attached in post #17 does not work, please show me which is not working.

[Fotis1991]

Hi to both of you.

I hope to don't mind to try to expalin where is the problem here.

AB33

Pls try this.

In the sample that apla posted in post#20 do this.

Choose a customer in B3

Notice that d2,d3 and perhaps d4 are automatic filled with products.

Fill B5 with an Order number.

In B6 type cash

In B7 put a number.

Type some quantities in E2,E3 AND IF D4 IS FILLED TYPE IN e4, too.

Click on CUT button.

All these data are tranfered in Results sheet.

Do another time the same-if you like-.

Now go to C30. Type an order number that exist in Column D of Results sheet.

Click on CALL BACK button. The order appears.

Click on DELETE ORDER button. The order is deleted from Sheet results.

Now go again to B3 and choose a customer name.

Nothing appears in D2,D3,D4....

Somehow your codes looks that delete the other code that using for filling with products the d2:d4 cells.

Hope that this helps.

[apla]

Thank you for one more time Foti.

This is exactly what i mean.

[nilem]

Hi Apla,
maybe so (in sheet "Orders" module)

Please Login or Register  to view this content.

[nilem]

... see also macro "SendToResult"

[apla]

Hi nilem!

What can i say? Both of your suggestions work great as i can see in my sample sheet.

Thank you so much for this.

Just one small issue if you are kind enough to take a look to it.

In D1 & D32 i need to have a header(Product).

When i choose a new customer in B3, these 2 headers, are dissapeared.

Can you do something for this pls?

[nilem]

Hi Apla,
see attachment. Hope this will help you.

[apla]

These are magics thinks!! How do you do these?

1 question and 1 small issue.

How do i call back an Order if i want to delete it?

I don't need the combo box in B5. In our case i want the user to type manually the Order number in that cell.

How can we fix it?

Thank you so much!

[nilem]

Just select the order number in the ComboBox and click the "Delete Order" button. Combo in B5 is needed to eliminate manual entry errors.
This is the usual approach for a model project of processing orders.
But you can make any changes if necessary.

[apla]

Thank you for your time and your efford. Really appreciated!

In our case we need to do a manual entry in B5 as we have the orders from another program and we need to keep these also in an Excel File. So no need of Combo box in B5. We don't need to check for manually errors.

Also as you'll see in the example sheet, in B5 i have choose Order number 2. Nothing appears and when i click the delete button the Msgbox(Do you want to delete appears, i click yes and nothing is deleted in results sheet.

[nilem]

Combo is filled with the activation of the "Orders" worksheet (see Private Sub Worksheet_Activate()). Activate worksheet and select "2" in combo to see the order details.

[apla]

Combo is filled with the activation of the "Orders" worksheet (see Private Sub Worksheet_Activate()). Activate worksheet and select "2" in combo to see the order details.

Ok. I got it. Thank you!

Although I dο not give up, but at the same time I do not want to become annoying. In no case.

That's why i try to ask again. Can we do this without the combo box in B5?

[nilem]

Can we do this without the combo box in B5?

Sure ......

[apla]

Appears to do nothing. What i do is this.

I type an Order number in B5, for calling back this specific order. Nothing appears even if i doumple click in B5.

I try to delete the order without to see it. Nothing deleted.

What am i missing here?

Also when i want to create a new order i don;t want B5 to "offer" me a suggestion of what order number should be.

Thanks for 1 more time.

[nilem]

I opened the file from post 37, put into B5-1, then 2, 4, 5 - and it works.
Are macros allowed in your Excel?
"i don;t want B5 to "offer" me a suggestion of what order number should be"
ignore it and enter your number.

[apla]

Really strange!!

Pls see what i mean.

Are macros allowed in your Excel?

Yes of course. All other macros works great.

[nilem]

Ok, I'll ask my friend AB to test this file

[Fotis1991]

nilem just to confirm that also i am not able to make it to works.

Perhaps some local settings? But how all the other macros work?

AB

Works for you?

[nilem]

That's it works for me

[apla]

Now i am really dissapointed!!

Such a great work and i am not able to use it!!

Yes nilem i watched your video and does exactly what i need. But i can not use it because does not works for me...

I appreciate your time and your patient. Also for you AB33.

Thanks Foti for trying to help me for one more time.

Let's hope that someone will have an idea for why this is not working for me.

Thank you.

[nilem]

I have only one idea. Maybe you have disabled events?
Try run this code (in a standard module)

Please Login or Register  to view this content.

and then enter a value in B5
And check out the names of the worksheets. They are written in English, not in Greek?

[AB33]

Yes, it works for me.
It is highly likely that events have been disabled, as Nilem indicated in #45
Please do the following.
Go to module itself then
Choose view from the menu and then Immediate window,
OR
while the cursor is at the module, type CRTL plus G, will take you to Immediate window.
At the Immediate window type
Application.EnableEvents = True
and hit the Enter button.
This will enable events.
Go back to the code and run it.

[apla]

Good morning and apologize for the delay in my reply. Too much work in the Office these days!

YES,YES,YES!

I used whe suggestion of AB33

while the cursor is at the module, type CRTL plus G, will take you to Immediate window.
At the Immediate window type
Application.EnableEvents = True
and hit the Enter button.
This will enable events.
Go back to the code and run it.

And works fine!

There are 2 small issues for beeing perfect but we can work even with these.

1) Put in B5 an order number that exist in Results sheet. Let's say 6.

In B7 appears the number 207! This is wrong. The correct one is 13.30 as you can see in the results sheet.

2) For a new order. Automatically appears a number in B5.

The user must start to type from Cell B3(Customer Name). Then the products are appered in column D. When user change the order number the products in column D are dissapeared and need to type again in B3 the customer name . Then the products in D columns are appered again and can continue to fill the order.

Thanks again.

[nilem]

...In B7 appears the number 207! This is wrong. The correct one is 13.30 as you can see in the results sheet...

Please Login or Register  to view this content.

I do not know how to count to 12, sorry
see attachment

[apla]

...I do not know how to count to 12, sorry

OOOOOhhhhh my mistake! i show to you. It's easy! 11 +1=12

Thank you for this. As you understand i try hard reading line by line your codes but i can not say that i understand everything!!

Any idea for my second question?

2)

The user must start to type from Cell B3(Customer Name). Then the products are appered in column D. When user change the order number the products in column D are dissapeared and need to type again in B3 the customer name . Then the products in D columns are appeared again and can continue to fill the order.

[nilem]

Apla, look again at the attachment in post 48. I changed the procedure Private Sub Worksheet_Change. Now it should work correctly

[apla]

No. Still not works. Unfortunately!!

If you feel tired with all this, please just tell me. I'll close the thread and i'll use my recorded macro for this. It will be one more click for the user.

Thank you

[nilem]

Apla, perhaps it would be better if you yourself will modify this project because I do not fully understand what you want, sorry

[apla]

Thanking you, AB33 & Foti, very-very much for everything that you done for me, i mark this thread as solved.

As my English and my VBA knowledge are not so good, i believe that i could not give you the correct explanations of what i need finally.

I do 1 more and last try to explain, in the language that you understand very well.tHE CODE language.

When i type the details of a new order in column B(b3:b7) then before to go in Column E to fill the quantities, i use this (recorded) macro AND EVERYTHING WORKS FINE.

Please Login or Register  to view this content.

Thank you all