VBA check boxes with auto updating text box

[Bill1989]

Hi, I am relatively new to VBA user forms, i am trying to create a form with multiple check boxes that will automatically update a text box at the bottom of the form with the total value associated with the check boxes that have been selected. I have no code to share at the moment as i am unsure where to start, any help would be greatly appreciated!

For example 4 check boxes:
Chocolate $10
Milk $5
Cheese $6
Steak $15

with the auto updating text box here to show the total of the checked boxes.

Thanks.

 

[ranman256]

each text box will run the total in its Afterupdate event:

Sub TotalAll()
Dim cSum As Currency

cSum = nz(txtBox1, 0)
cSum = cSum + nz(txtBox2, 0)
cSum = cSum + nz(txtBox3, 0)
cSum = cSum + nz(txtBox4, 0)
txtTotal = cSum
End Sub

Public Sub txtBox1_afterupdate()
TotalAll
End Sub

Public Sub txtBox2_afterupdate()
TotalAll
End Sub

 

[MickG]

Try this in you Userform Module:-
The Totals from the values posted in each checkbox caption are returned in TextBox1.
NB:- Add "Checkboxes" as required

[COLOR="Navy"]Sub[/COLOR] tot(TB [COLOR="Navy"]As[/COLOR] Object)
[COLOR="Navy"]Dim[/COLOR] Ctrl [COLOR="Navy"]As[/COLOR] Control, nTot [COLOR="Navy"]As[/COLOR] Double
TextBox1 = ""
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ctrl [COLOR="Navy"]In[/COLOR] Me.Controls
    [COLOR="Navy"]If[/COLOR] TypeName(Ctrl) = "CheckBox" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Ctrl = True [COLOR="Navy"]Then[/COLOR]
            nTot = nTot + Val(Mid(Split(Ctrl.Caption, " ")(1), 2))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ctrl
TextBox1 = Format(nTot, "$0.0")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox1_Click()
tot CheckBox1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_Click()
tot CheckBox2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox3_Click()
tot CheckBox3
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox4_Click()
tot CheckBox4
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Bill1989]

Thanks, works perfectly! My only other question, I tried to use this in a multi-page user form, both pages with the same style, however the text boxes were linked and so showing a total from both pages, do you know of a way to keep the textboxes unique to that page of the multi page?

 

[MickG]

Try this for Multipages (1 & 2)
The results For Page1 are on Textbox1 on Page1 and the results For Page2 are in Textbox2 on Page2.
Add Checkboxes as you like, the code shows 8 Checkboxes, 4 on each Page.

[COLOR="Navy"]Sub[/COLOR] tot()
[COLOR="Navy"]Dim[/COLOR] ctrl [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]Dim[/COLOR] P1 [COLOR="Navy"]As[/COLOR] Double, P2 [COLOR="Navy"]As[/COLOR] Double
TextBox1 = "": TextBox2 = ""
P1 = 0: P2 = 0

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ctrl [COLOR="Navy"]In[/COLOR] Controls
    [COLOR="Navy"]If[/COLOR] TypeName(ctrl) = "CheckBox" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] ctrl.Parent.Name = "Page1" And ctrl = True [COLOR="Navy"]Then[/COLOR]
            P1 = P1 + Val(Mid(Split(ctrl.Caption, " ")(1), 2))
    [COLOR="Navy"]ElseIf[/COLOR] ctrl.Parent.Name = "Page2" And ctrl = True [COLOR="Navy"]Then[/COLOR]
        P2 = P2 + Val(Mid(Split(ctrl.Caption, " ")(1), 2))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] ctrl

[COLOR="Navy"]If[/COLOR] Me.MultiPage1.Value = 0 [COLOR="Navy"]Then[/COLOR]
    TextBox1.Value = Format(P1, "$0.0")
[COLOR="Navy"]Else[/COLOR]
    TextBox2.Value = Format(P2, "$0.0")
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox1_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox2_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox3_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox4_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox5_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox6_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox7_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CheckBox8_Click()
tot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick