Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code for a custom function?
I'm really new at this VBA code thing and have found almost everything
I need here from this group just don't understand some of the complex things: Let me explain my problem I have a userform built in VBA that has the following cboline: combo box that list out production lines cbomen: combo box that is the number of men on the production line txtmin: The number of minutes the line was down txtwait: a value that represent time the line waited on a part txtmcost: the cost of the material txtvcost: the cost for a outside vendor txtcost: total cost of downtime My big issue is each (cboline)&(cbomen) has a constant value: example line 1 = $100 line 2 = $150 line 3 = $200 line1 workers value (cbomen) = $12 line2 workers value (cbomen) = $13.25 I'm trying to find the total cost based on downtime for each line example of my issue: Line 1 (cboline) is down for (txtmin) 120 minutes but minus the (txtwait) 15 minutes because of waiting on parts then take that value and add the cost of (cbomen) each man working on the line and also add the material cost (txtmcost) and vendor cost (txtvcost) for a total cost (txtcost) of the downtime. I've been working on this for days using this forum but no luck and I'm nearing the deadline for this project. I would have down all these formulas in a worksheet but this needs to behind the userform because it gives values of the men working and it not something that should be viewed by the people using the form. Thanks in advance Chuck |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code for a custom function?
There is code behind each item in the forms (combo box). If you use the
right mouse button on the form there is an option to see the code. this code is listed as private. You can make this public so these variable are seen in other parts of the VBA. I have taken Variable from the main VBA code and used it inside the form code. " wrote: I'm really new at this VBA code thing and have found almost everything I need here from this group just don't understand some of the complex things: Let me explain my problem I have a userform built in VBA that has the following cboline: combo box that list out production lines cbomen: combo box that is the number of men on the production line txtmin: The number of minutes the line was down txtwait: a value that represent time the line waited on a part txtmcost: the cost of the material txtvcost: the cost for a outside vendor txtcost: total cost of downtime My big issue is each (cboline)&(cbomen) has a constant value: example line 1 = $100 line 2 = $150 line 3 = $200 line1 workers value (cbomen) = $12 line2 workers value (cbomen) = $13.25 I'm trying to find the total cost based on downtime for each line example of my issue: Line 1 (cboline) is down for (txtmin) 120 minutes but minus the (txtwait) 15 minutes because of waiting on parts then take that value and add the cost of (cbomen) each man working on the line and also add the material cost (txtmcost) and vendor cost (txtvcost) for a total cost (txtcost) of the downtime. I've been working on this for days using this forum but no luck and I'm nearing the deadline for this project. I would have down all these formulas in a worksheet but this needs to behind the userform because it gives values of the men working and it not something that should be viewed by the people using the form. Thanks in advance Chuck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code for a custom function?
You description isn't totally clear how the 100/150/200 is used/applied, but
Private Sub Commandbutton1_click() Dim rate as double, fixedcost as double Select Case trim(lcase(cboLine.Text)) Case "line 1" rate = 12 fixedcost = 100 Case "line 2" rate = 13.25 fixedcost = 150 case "line 3" rate = 14.00 fixedcost = 200 Case else ' default values - optional rate = 10 fixedcost = 0 end Select txtCost.Text = format(fixedCost + (cdbl(txtmin.Text) - _ cdbl(txtWait.Text)) * clng(cbomen.Text) * rate + _ cdbl(txtmcost.text) + dbld(txtvcost.text), "$ #,##0.00") End Sub If your rates are per hour, then divide by 60 since your times are in minutes. assumes you enter line 1 in the cboline box. Adjust to match your entries. -- Regards, Tom Ogilvy wrote in message oups.com... I'm really new at this VBA code thing and have found almost everything I need here from this group just don't understand some of the complex things: Let me explain my problem I have a userform built in VBA that has the following cboline: combo box that list out production lines cbomen: combo box that is the number of men on the production line txtmin: The number of minutes the line was down txtwait: a value that represent time the line waited on a part txtmcost: the cost of the material txtvcost: the cost for a outside vendor txtcost: total cost of downtime My big issue is each (cboline)&(cbomen) has a constant value: example line 1 = $100 line 2 = $150 line 3 = $200 line1 workers value (cbomen) = $12 line2 workers value (cbomen) = $13.25 I'm trying to find the total cost based on downtime for each line example of my issue: Line 1 (cboline) is down for (txtmin) 120 minutes but minus the (txtwait) 15 minutes because of waiting on parts then take that value and add the cost of (cbomen) each man working on the line and also add the material cost (txtmcost) and vendor cost (txtvcost) for a total cost (txtcost) of the downtime. I've been working on this for days using this forum but no luck and I'm nearing the deadline for this project. I would have down all these formulas in a worksheet but this needs to behind the userform because it gives values of the men working and it not something that should be viewed by the people using the form. Thanks in advance Chuck |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need VBA code for a custom function?
On Feb 17, 1:29 pm, "Tom Ogilvy" wrote:
You description isn't totally clear how the 100/150/200 is used/applied, but Private Sub Commandbutton1_click() Dim rate as double, fixedcost as double Select Case trim(lcase(cboLine.Text)) Case "line 1" rate = 12 fixedcost = 100 Case "line 2" rate = 13.25 fixedcost = 150 case "line 3" rate = 14.00 fixedcost = 200 Case else ' default values - optional rate = 10 fixedcost = 0 end Select txtCost.Text = format(fixedCost + (cdbl(txtmin.Text) - _ cdbl(txtWait.Text)) * clng(cbomen.Text) * rate + _ cdbl(txtmcost.text) + dbld(txtvcost.text), "$ #,##0.00") End Sub If your rates are per hour, then divide by 60 since your times are in minutes. assumes you enter line 1 in the cboline box. Adjust to match your entries. -- Regards, Tom Ogilvy wrote in message oups.com... I'm really new at this VBA code thing and have found almost everything I need here from this group just don't understand some of the complex things: Let me explain my problem I have a userform built in VBA that has the following cboline: combo box that list out production lines cbomen: combo box that is the number of men on the production line txtmin: The number of minutes the line was down txtwait: a value that represent time the line waited on a part txtmcost: the cost of the material txtvcost: the cost for a outside vendor txtcost: total cost of downtime My big issue is each (cboline)&(cbomen) has a constant value: example line 1 = $100 line 2 = $150 line 3 = $200 line1 workers value (cbomen) = $12 line2 workers value (cbomen) = $13.25 I'm trying to find the total cost based on downtime for each line example of my issue: Line 1 (cboline) is down for (txtmin) 120 minutes but minus the (txtwait) 15 minutes because of waiting on parts then take that value and add the cost of (cbomen) each man working on the line and also add the material cost (txtmcost) and vendor cost (txtvcost) for a total cost (txtcost) of the downtime. I've been working on this for days using this forum but no luck and I'm nearing the deadline for this project. I would have down all these formulas in a worksheet but this needs to behind the userform because it gives values of the men working and it not something that should be viewed by the people using the form. Thanks in advance Chuck- Hide quoted text - - Show quoted text - Thanks Tom, I've used the code and doesn't pick up the fixed cost portion also sorry about not be clear the rates and fixed cost are per hour: If I walk thru the process in the debug stage it show fixed cost = 0 what did I do wrong? Thanks once again Chuck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert function - custom function name preceded by module name | Excel Programming | |||
can't find custom function code | Excel Worksheet Functions | |||
custom function-code for percentage | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |