Referencing command buttons within if-then-else

Hi guys & gals,

I made a userform to calculate the net weight of a package by taking the gross weight and deducting the amount of boxes included in the package. The most boxes possible is 25, so I used 25 command buttons numbered 1 to 25, so users only have to press the desired amount of boxes for the calculation to begin. But that also means I've got 25 subs containing the same formula, and that's a bit much. I'm pretty sure this could be done more efficiently, but I'm not sure what's possible, me not being a VB kind o' guy...

I included the sheet, so if anybody has a better way of doing this, it would be appreciated!

Good morning opopanax666

Originally Posted by opopanax666

I'm pretty sure this could be done more efficiently, but I'm not sure what's possible, me not being a VB kind o' guy...

The file you've uploaded is an Excel 2007 file, and I only have 2003 at work, so I can't bring this into your file, but have a look at the example attached. It contains a simple userform with 8 buttons and a class module to catch the ID of each command button clicked : would this be of any use to you?

More on classes.

HTH

DominicB

Originally Posted by dominicb

It contains a simple userform with 8 buttons and a class module to catch the ID of each command button clicked : would this be of any use to you?

Yes, I think I will go the "class" way. Thanks a lot for the speedy reply!

Originally Posted by Andy Pope

Or you could go with a class and put all of the code in there but that is quite a complex approach.

I noticed if you press a quantity button multiple times the total keeps on changing, if this to be expected?

This is a learning experience, so "classes" it is. About the multiple quantities: yes, this is normal given that a package can contain different kinds of boxes... Thanks for the quick reply!

There are a number of ways to tackle this.

First and simplest would be to use a dropdown list in order to select quantity.
This would then only require 1 routine.

Or you could move all of the code from a click event in to a routine where you pass the quantity as an argument from the click event

Single calculating routine

Private Sub m_Calculate(BRUTO)

If optDU = True Then
        TC = Worksheets("Netto").Range("B21").Value
    ElseIf optDK = True Then
        TC = Worksheets("Netto").Range("B12").Value
    ElseIf optRD = True Then
        TC = Worksheets("Netto").Range("B13").Value
    ElseIf optRT = True Then
        TC = Worksheets("Netto").Range("B14").Value
    ElseIf optRE = True Then
        TC = Worksheets("Netto").Range("B15").Value
    ElseIf optBE = True Then
        TC = Worksheets("Netto").Range("B18").Value
    ElseIf optSL = True Then
        TC = Worksheets("Netto").Range("B26").Value
    ElseIf optKO = True Then
        TC = Worksheets("Netto").Range("B16").Value
    ElseIf optKG = True Then
        TC = Worksheets("Netto").Range("B17").Value
    ElseIf optBP = True Then
        TC = Worksheets("Netto").Range("B19").Value
    ElseIf optGK = True Then
        TC = Worksheets("Netto").Range("B27").Value
    ElseIf optEuro = True Then
        TC = Worksheets("Netto").Range("B1").Value
    ElseIf optWW = True Then
        TC = Worksheets("Netto").Range("B2").Value
    ElseIf optEuro16 = True Then
        TC = Worksheets("Netto").Range("B24").Value
    ElseIf optWW16 = True Then
        TC = Worksheets("Netto").Range("B23").Value
    ElseIf optBK = True Then
        TC = Worksheets("Netto").Range("B29").Value
    ElseIf optBK2 = True Then
        TC = Worksheets("Netto").Range("B28").Value
    Else
        TC = 0
End If

AANTAL = 1 'knop "1" gedrukt

    If optRBT = True Then
        TB = Worksheets("Netto").Range("B4").Value
    ElseIf optRBH = True Then
        TB = Worksheets("Netto").Range("B5").Value
    ElseIf optBAN = True Then
        TB = Worksheets("Netto").Range("B7").Value
    ElseIf optGB = True Then
        TB = Worksheets("Netto").Range("B6").Value
    ElseIf optAB = True Then
        TB = Worksheets("Netto").Range("B30").Value
    ElseIf optGPHR = True Then
        TB = Worksheets("Netto").Range("B9").Value
    ElseIf optHDT = True Then
        TB = Worksheets("Netto").Range("B10").Value
    Else
        TB = 0
End If

If VLAG = 0 Then
        TARRA = TARRA + TC + (TB * AANTAL)
        VLAG = 1
    ElseIf VLAG = 1 Then
        TARRA = TARRA + (TB * AANTAL)
End If

NETTO = BRUTO - TARRA

txtAantal.Value = AANTAL
txtTarra.Text = Format(TARRA, "#.0")
txtNetto.Text = Format(NETTO, "#.0")

End Sub

Click events would look like this, although you will still need 25 calls to the m_Calculate routine.

Private Sub comEen_Click()

    m_Calculate Val(txtBruto.Text)

End Sub

Or you could go with a class and put all of the code in there but that is quite a complex approach.

I noticed if you press a quantity button multiple times the total keeps on changing, if this to be expected?