Solved: Decrease qty thru msgbox

[ndendrinos]

On sheet “Invoice” [A17:A35] I type a quantity
A userform opens and from a listbox I choose a product
Example:

……..A……..B
15…100……Pen
16
17

Before running the code I had 90 Pens in stock

I run the code and this quantity gets deducted from sheet “stock” decreasing the corresponding product by 100

Since I just deducted 100 I now have a deficit of -10 (this shows on row1 and is a FORMULA …. The product are listed on row4)

So because of this a message on sheet “Invoice” a message box pops up advising of the deficient Product as well as the qty missing to fill the order and another message box follows giving the user 2 options. Yes/No

The No code I will be able to handle because it will be based on the same principle as
the Yes code.

The Yes Code needs to first delete the last row on sheet “Stock” this I can do.
It also needs to identify the Product on Row4 (and now this is my scenario) go up 3 rows
copy the formula cell (90) pastespecial/value same out of range somewhere (either sheet “Stock” or “Invoice” copy same then identify the product on column B [B15:B35] select
adjacent cell to the left and paste.

If you’ve read this message and are interested in helping I attach a workbook
Thank you

[Simon Lloyd]

Why copy and paste the formula?, you say to somewhere - where would you want it copied to?, why go up 3 rows or is it always the first row for that product that you want the formula from?, why not just put the formula on the sheet you were going to paste to, the formula would always be a live indication of row 1 for that product.

[ndendrinos]

Hello Simon

Why copy and paste the formula?, you say to somewhere - where would you want it copied to?
In the attached file:
In Sheet "Stock" I tried to manually copy [O1] (that is 3 rows over Pen) and special pasting/value same on sheet "Invoice" [A20] and it does not work.

But If I special paste/value anywhere else (it really does not matter where for having done that I cut that cell and paste it to [A20] and now it works.

As for your suggestion then yes I could link the top & Fourth rows to 2 rows in sheet "Invoice" further down the Invoice. (good idea)


Just tried copypaste/special from the linked cells in sheet "Invoice" and it works!!! np need to copy elsewhere now.

[ndendrinos]

Simon just thought of replacing the last msgbox with a userform with a list box linked to sheet "Product" hence the selection of the product that needs the quantities ordered deceased could be selected from there.
Maybe you can come up with something else

[Simon Lloyd]

Im sure i could, if i had the time :), you'd be better off carefully explaining what you want to happen, when and how you will trigger that event....etc, if you explain clearly in a very simple way we can help you build what you need.

Often people confuse the issues by telling us their version straight from their head and it usually gets very clouded on the way out ;)

[ndendrinos]

Simon: I thought I was pretty clear in the body of my post as to what I am trying to achieve

And I did include a detailed workbook for further clarity.

Anyhow I figured it on my own (convoluted as it may be ,,, but it works)
I include again a workbook to show what I've come up with and also a request for help like this:

Is there a way to combine the codes of the two command buttons of the userform into one.

Thank you
Nick

[ndendrinos]

and here the two codes I'm trying to fuse in one

Private Sub CommandButton1_Click()
For Each cell In [A5:G5]
If Me.ListBox1.Value = cell.Value Then
cell.Activate
ActiveCell.Offset(-1, 0).Select
Selection.Copy
Exit Sub
End If
Next
End Sub

and


Private Sub CommandButton2_Click()
For Each cell In [L5:L11]
If Me.ListBox1.Value = cell.Value Then
cell.Activate
ActiveCell.Offset(0, -1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Unload UserForm1
Exit Sub
End If
Next

End Sub

[Simon Lloyd]

Can i ask why you have chosen to create a For...Next loop but then put exit sub in it?, this will prevent the loop from continuing past the first action.

[ndendrinos]

I see what you mean and will remove the "exit subs" as to why they are there it is simply due to my ignorance ... mind you I don't see why continuing the loop would matter in this instance for the userform is not setup to deal with more than a single understocked product. Would be nice to be able to handle more than one product at a time.
Thanks

[Simon Lloyd]

Well in that case throw away both your command button codes and use this:
Private Sub CommandButton1_Click()
Dim fCell As Range, nCell As Range
Set fCell = Sheets("Invoice").Range("A1:G5").Find(What:=Me.ListBox1.Value, After:=Sheets("Invoice").Range("A1"))
fCell.Offset(-1, 0).Copy
Set nCell = Sheets("Invoice").Range("L4:L11").Find(What:=Me.ListBox1.Value, After:=Sheets("Invoice").Range("L4"))
nCell.Offset(0, -1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Unload UserForm1
End Sub

[ndendrinos]

EXCELLENT ! and have a very good evening.

[Simon Lloyd]

EXCELLENT ! and have a very good evening.Why not check out the workbook i've attached, you can possible do away with VBA for that task!

[ndendrinos]

It is very interesting ...
The reasons I would prefer to stick with what I have are like this:

First: I'm too old to see the font in the drop downs.

Second: Where would I see the available quantities and decide to decrease the order OR cancel it?

Third you forget but last night you've helped me (on your site) in setting up a whole series of warnings vis a vis the stock availability
(warning to reoder as the stock was getting near the minimum acceptable threshold for a given item. / warning when there is not enough stock to fill the order)
I really need all I have if I'm going to have a semblance of inventory control in this workbook.

Lastly it is also a mater of preference ... I like userform and hate the little drop down arrows of validated cells.

BUT I do appreciate your time in offering a more logical approach and thank you for it.