Set multiple values simultaniously in UserFrom

[percy83]

Hi all,

I am working on simple freight cost calculator and is currenly designing a simple user form to enter package size and measurements and weight.

It consists of five rows were you can select no of packages, lenght, width and height.

I also want to set all text boxes to the left as default "1" with the userforminitialize event. I've named them pcs1 to pcs5. How is the best way to set all these values at the same time?

What is the easiest way to make a summary out of this? Is it naming and looping through the different text boxes or does anyone have a better idea?

What I want is total weight and volume.

Thanks for all help I can get!

BR
Percy

 

[shg]

Using a userform at all seems like overkill for this; you could just use worksheet cells and formula.

That said, you can put the initial values in at design time, and that's what will appear when you show the form.

 

[percy83]

Using a userform at all seems like overkill for this; you could just use worksheet cells and formula.

That said, you can put the initial values in at design time, and that's what will appear when you show the form.

I totally get your point but this is a part of an application and this uf is triggered with a button if you have more then one parcel.

Also, what is the easiest way to get total weight and volume based on the uf solution?

Thanks!

 

[mikerickson]

I also want to set all text boxes to the left as default "1" with the userforminitialize event. I've named them pcs1 to pcs5. How is the best way to set all these values at the same time?

You could set them now as the default values. Set the text property of the controls in the Properties Window to 1.

 

[percy83]

You could set them now as the default values. Set the text property of the controls in the Properties Window to 1.

Perfect, thanks. Do you think it is easier to make the summary with formulas in the userform itself of is it betterto export the individual values to a sheet in the workbook and make the summary there?

Thanks!

 

[mikerickson]

I would want to put these functions in the userform code module, adjusting the control names to meet your situation.

Function TotalVolume() As Double
    TotalVolume = Val(tbxHeight1.Text) * Val(tbxWidth1.Text) * Val(tbxDepth1.Text) * Val(pcs1.Text)
    TotalVolume = TotalVolume + Val(tbxHeight2.Text) * Val(tbxWidth2.Text) * Val(tbxDepth2.Text) * Val(pcs2.Text)
    TotalVolume = TotalVolume + Val(tbxHeight3.Text) * Val(tbxWidth3.Text) * Val(tbxDepth3.Text) * Val(pcs3.Text)
    TotalVolume = TotalVolume + Val(tbxHeight4.Text) * Val(tbxWidth4.Text) * Val(tbxDepth4.Text) * Val(pcs4.Text)
    TotalVolume = TotalVolume + Val(tbxHeight5.Text) * Val(tbxWidth5.Text) * Val(tbxDepth5.Text) * Val(pcs5.Text)
End Function

Function TotalWeight() As Double
    TotalWeight = Val(tbxWeight1.Text) * Val(pcs1.Text) + Val(tbxWeight2.Text) * Val(pcs2.Text) _
                   + Val(tbxWeight3.Text) * Val(pcs3.Text) + Val(tbxWeight4.Text) * Val(pcs2.Text) _
                   + Val(tbxWeight5.Text) * Val(pcs5.Text)
End Function