Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A Form
I have created a simple inventory form. I am not very familiar with VBE, but
found a simple code I can use to run it. How do I add to the code to sum the total of parts available, i.e. =sumif(a2:A10="New entry")? Here is the form layout: Part No: Location: Date Received: Number Recieved: Amount On Hand: (Need formula) Here is the code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.txtPart.Value) = "" Then Me.txtPart.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPart.Value ws.Cells(iRow, 2).Value = Me.txtLoc.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.txtQty.Value 'clear the data Me.txtPart.Value = "" Me.txtLoc.Value = "" Me.txtDate.Value = "" Me.txtQty.Value = "" Me.txtPart.SetFocus End Sub Thanks for all your help! Johnnie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A Form
create a label on your form, and everytime you add an item, update the
label; example code: result = Application.WorksheetFunction.Sum( <= add your arguments here for what you want to sum, YourForm.lblCalculated.Caption = "Value is : " & result "Johnnie" wrote in message ... I have created a simple inventory form. I am not very familiar with VBE, but found a simple code I can use to run it. How do I add to the code to sum the total of parts available, i.e. =sumif(a2:A10="New entry")? Here is the form layout: Part No: Location: Date Received: Number Recieved: Amount On Hand: (Need formula) Here is the code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.txtPart.Value) = "" Then Me.txtPart.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPart.Value ws.Cells(iRow, 2).Value = Me.txtLoc.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.txtQty.Value 'clear the data Me.txtPart.Value = "" Me.txtLoc.Value = "" Me.txtDate.Value = "" Me.txtQty.Value = "" Me.txtPart.SetFocus End Sub Thanks for all your help! Johnnie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating A Form
Tieski,
Thanks for the response. However, I realized I did not phrase the request for assistance correctly. I need help in creating a new line on the that, when a new order is entered, the code will search to see if the part number already exists and the do calculation for only those line items which contain that part number and give a total on hand. If you think this is to complicated for a novice like me, please let me know and I will just work with the spreadsheet view. Once again, thanks for all your help. "Tieske" wrote: create a label on your form, and everytime you add an item, update the label; example code: result = Application.WorksheetFunction.Sum( <= add your arguments here for what you want to sum, YourForm.lblCalculated.Caption = "Value is : " & result "Johnnie" wrote in message ... I have created a simple inventory form. I am not very familiar with VBE, but found a simple code I can use to run it. How do I add to the code to sum the total of parts available, i.e. =sumif(a2:A10="New entry")? Here is the form layout: Part No: Location: Date Received: Number Recieved: Amount On Hand: (Need formula) Here is the code: Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.txtPart.Value) = "" Then Me.txtPart.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPart.Value ws.Cells(iRow, 2).Value = Me.txtLoc.Value ws.Cells(iRow, 3).Value = Me.txtDate.Value ws.Cells(iRow, 4).Value = Me.txtQty.Value 'clear the data Me.txtPart.Value = "" Me.txtLoc.Value = "" Me.txtDate.Value = "" Me.txtQty.Value = "" Me.txtPart.SetFocus End Sub Thanks for all your help! Johnnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Form | Excel Worksheet Functions | |||
Creating a form | Excel Worksheet Functions | |||
creating a form | New Users to Excel | |||
Creating a Form | Excel Discussion (Misc queries) | |||
Creating a Search Form | Excel Discussion (Misc queries) |