Update Userform

[poojavasti]

I have Excel-2007

I have entries in one sheet whose name I have given

as Records where I have 16 columns.

First column is named ProductCode which has all

numeric number which are unique. I have approx 10000+ records.

One of the column is named Qnty.

What I want is in sheet2 a userform kind of system where

I have three options

1) Code : where I will enter the ProductCode number which is

already there in Record sheet.

2) Add : where I will enter some number.

3) Deduct: where I will enter some number.


Last there will be Update button.


After entering the Code number I will enter some number in Add or Deduct

button. If Add then it should add that number to the existing number in the Qnty sheet.

i.e for eg if that matching Code has in Qnty 5 and I Add 2 and give Update then it should become 7

in Record sheet.

Similarly may be in Deduct I give 2 and Update then it will become 3.

BUT if in Deduct I give 6, then it will prompt me with RECORD MISS-MATCH,

which I will check myself and decide what to do.

Presently I am scrolling up and down the numbers and manually adding and deducting

which is taking too much of time. This will save my time and help me to do other imp work.

If someone can provide me with the solution.

thanks

 

[Luke M]

That is certainly doable, but before we begin, may I make a suggestion? As errors do occur, especially when humans get involved <grin> it is usually better to only generate "new" data, rather than overwriting previous data.

I.e., rather than changing the number on your data sheet, treat it as a running total, with entries like

[pre]

Eggs   3
Bread  2
Eggs   -2
Eggs   4
Bread  2

[/pre]
And then create a report (PivotTable, dashboard, etc) that sums up each category and lets you know that you have 5 eggs and 4 bread. I'd also date stamp each entry. The advantage of this is that it becomes easier to audit when something goes wrong and you can find your mistake.


If you like this idea, Debra has an excellent write-up on how to setup your workbook here:

http://blog.contextures.com/archives/2012/01/12/excel-userform-data-entry-update/

 

[poojavasti]

This is just an example

[pre]

Code    ItemName      Pkd          Qnty
55093    Eggs        Jan-2013       5
55094    Bread       Jan-2013       10
55095    Butter      Jan-2013       8
55096    Oil         Jan-2013       12
55097
55098

[/pre]
As you see every Item has a unique code.

I cannot use 55097 again for Eggs and

55098 for Bread. It has

to be used only for the product which is

new and not in the list. As per the above

Eggs and Bread example it should Add or

Deduct Qnty in 55093 and 55094 only.

 

[Luke M]

You'll need to build the form and customize this to suit the actual structure, but here's a simple layout to get you started.

[pre]

'Assumes a userform with 3 text entry boxes, 1 command button
'text1 name is pCode
'text2 name is qAdd
'text3 name is qDeduct
'command button name is myUpdate
Private Sub myUpdate_Click()
Dim myRow As Long
Dim myCode As Long
Dim curQty As Long
Dim PosNum As Long, NegNum As Long

On Error Resume Next
PosNum = Me.qAdd.Value
NegNum = Me.qDeduct.Value
On Error GoTo 0

'Where is the data?
With Worksheets("Records")
myCode = Me.pCode.Value
On Error Resume Next
'Assumes product code is in col A
myRow = WorksheetFunction.Match(myCode, .Range("A:A"), 0)
On Error GoTo 0

If myRow < 1 Then
MsgBox "Invalid Product Code", vbCritical + vbOKOnly, "Product Error"
Exit Sub
End If

'Assumes current qty is in col D
curQty = .Cells(myRow, "D").Value

If curQty + PosNum - NegNum < 0 Then
'too little!
MsgBox "RECORD MISS-MATCH!", vbCritical + vbOKOnly, "INVALID"
Exit Sub
Else
.Cells(myRow, "D").Value = curQty + PosNum - NegNum
End If
End With

With Me
.pCode.Value = ""
.qAdd.Value = 0
.qDeduct.Value = 0
.pCode.SetFocus
End With

End Sub

[/pre]