Need VBA code for a custom function?

[crkinney]

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

 

[Joel]

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.

 

[Tom Ogilvy]

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.

 

[crkinney]

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






- 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

 

[Tom Ogilvy]

does cboline containt text that matches the case statement?

Line 1
Line 2
Line 3

as you showed in your original post and I advised you would need to be
matched in the case statement?

Not that I have made the value all lowercase and trimmed off any spaces on
the front or back to make it more robust - so if you changed the

Case "line 1"

to something like

Case "AutoMotive" (as an example), then that would never be matched,
because the select Case statement would actually be looking for all lower
case "automotive"

If you can't figure it out, send me a sample workbook to (e-mail address removed)
and I will try to set it up for you.