VBA - user form for stock control

[ricktate1810]

Hi,

I am new to VBA and need some help.

I have a user form designed to enter:

Stock code
Price
Qty
Postage cost

I have it set up so that when you enter:
Stock code - value goes to sheet (sales) and enters the value into next empty cell column a
price - column b
qty - column d
postage cost - column c


i have a save button and when i click this the above details copy into the sheet i want perfectly.

What i now need is when this button is pressed it also deducts the qty sold from the second sheet (called stock)

So for example if i entered stock code "abc" and qty "5" it will then look up "abc" on the stock sheet in column a and minus qty "5" from column b of the corresponding stock code.

Does that make sense?

I would appreciate any help you can offer!

 

[ranman256]

using a form with text boxes:
txtTicker
txtPrice

run PostStock on the button click:

Public Sub PostStock()

If FindStock(txtTicker) Then
   ActiveCell.Offset(0, 2).Value = l.Offset(0, 2).Value - txtPrice          'reduce the qty
Else
  MsgBox txtticker & " not found", vbCritical, "Fail"
End If
End Sub


Function FindStock(ByVal pvCode) As Boolean


On Error GoTo errFind


    Columns("A:A").Select
    Selection.Find(What:=pvCode, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
FindStock = True
Exit Function
errFind:
End Function

 

[ricktate1810]

Hi,

I'm not sure where I should put this code - this is the code I have at the moment for my save/new record button:

(any pointers appreciated!)

Private Sub saveclose_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sales")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.StockCode.Value) = "" Then
Me.StockCode.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.StockCode.Value
ws.Cells(iRow, 2).Value = Me.price.Value
ws.Cells(iRow, 3).Value = Me.post.Value
ws.Cells(iRow, 4).Value = Me.qty.Value

'clear the data
Me.StockCode.Value = ""
Me.qty.Value = ""
Me.post.Value = ""
Me.price.Value = ""
End Sub
Private Sub savenew_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sales")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.StockCode.Value) = "" Then
Me.StockCode.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.StockCode.Value
ws.Cells(iRow, 2).Value = Me.price.Value
ws.Cells(iRow, 3).Value = Me.post.Value
ws.Cells(iRow, 4).Value = Me.qty.Value

'clear the data
Me.StockCode.Value = ""
Me.qty.Value = ""
Me.post.Value = ""
Me.price.Value = ""
ThisWorkbook.Save
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub

using a form with text boxes:
txtTicker
txtPrice

run PostStock on the button click:

Public Sub PostStock()

If FindStock(txtTicker) Then
   ActiveCell.Offset(0, 2).Value = l.Offset(0, 2).Value - txtPrice          'reduce the qty
Else
  MsgBox txtticker & " not found", vbCritical, "Fail"
End If
End Sub


Function FindStock(ByVal pvCode) As Boolean


On Error GoTo errFind


    Columns("A:A").Select
    Selection.Find(What:=pvCode, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
FindStock = True
Exit Function
errFind:
End Function