Userform textbox values

Stevie p

Board Regular
Joined
Mar 8, 2009
Messages
228
Hi,
I have a userform ("userform1") within this form i have textbox 1&2 recording how many incidents, textbox 3 to 7 record type of incident.
The combined value of textbox 3to7 should give the same combined value as textbox 1&2. What i need if possible if these two values are not the same then message box Error "Values added".

Regards Stevie p
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, If by combined Values you mean Sum then try This:-
I Imagine this code will run with another code on the userform when the All the TextBox Values are entered.
Code:
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Tb [COLOR="Navy"]As[/COLOR] Control, oSum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iSum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Tb [COLOR="Navy"]In[/COLOR] Me.Controls
    [COLOR="Navy"]If[/COLOR] Tb.Value <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Right(Tb.Name, 1) > 0 And Right(Tb.Name, 1) < 3 [COLOR="Navy"]Then[/COLOR]
            iSum = iSum + Tb.Value
        [COLOR="Navy"]ElseIf[/COLOR] Right(Tb.Name, 1) > 2 And Right(Tb.Name, 1) < 9 [COLOR="Navy"]Then[/COLOR]
            oSum = oSum + Tb.Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Tb
[COLOR="Navy"]If[/COLOR] Not oSum = iSum [COLOR="Navy"]Then[/COLOR] MsgBox "Data [COLOR="Navy"]Is[/COLOR] not Equal"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Stevie p

Board Regular
Joined
Mar 8, 2009
Messages
228
This is te userform code but not sure where you addition would fit into it? could you help please.

Private Sub CommandButton1_Click()
Sheets("LIST2").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell = UserForm1.TextBox11.Value
ActiveCell.Offset(0, 1) = UserForm1.ComboBox1.Value
ActiveCell.Offset(0, 2) = TextBox1.Value
ActiveCell.Offset(0, 3) = TextBox2.Value
ActiveCell.Offset(0, 4) = TextBox3.Value
ActiveCell.Offset(0, 5) = TextBox4.Value
ActiveCell.Offset(0, 6).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])"
ActiveCell.Offset(0, 1) = TextBox5.Value
ActiveCell.Offset(0, 2) = TextBox6.Value
ActiveCell.Offset(0, 3) = TextBox7.Value
ActiveCell.Offset(0, 4) = TextBox8.Value
ActiveCell.Offset(0, 5) = TextBox9.Value
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 3) = UserForm1.ComboBox2.Value
ActiveCell.Offset(0, 4) = UserForm1.TextBox6.Value
ActiveCell.Offset(0, 5) = UserForm1.TextBox7.Value
ActiveCell.Offset(0, 6) = UserForm1.TextBox8.Value
Sheets("MAIN").Select
End Sub
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, You can just place it at the top of the Code, which I have altered slightly to exit the sub if the conditionis not met.
Code:
Private Sub CommandButton2_Click()
Dim Tb As Control, oSum As Long, iSum As Long
For Each Tb In Me.Controls
    If Tb.Value <> "" Then
        If Right(Tb.Name, 1) > 0 And Right(Tb.Name, 1) < 3 Then
            iSum = iSum + Tb.Value
        ElseIf Right(Tb.Name, 1) > 2 And Right(Tb.Name, 1) < 9 Then
            oSum = oSum + Tb.Value
        End If
    End If
Next Tb
If Not oSum = iSum Then
MsgBox "Data Is not Equal"
Exit Sub
 End If
Sheets("List2").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell = UserForm1.TextBox11.Value
ActiveCell.Offset(0, 1) = UserForm1.ComboBox1.Value
ActiveCell.Offset(0, 2) = TextBox1.Value
ActiveCell.Offset(0, 3) = TextBox2.Value
ActiveCell.Offset(0, 4) = TextBox3.Value
ActiveCell.Offset(0, 5) = TextBox4.Value
ActiveCell.Offset(0, 6).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-3])"
ActiveCell.Offset(0, 1) = TextBox5.Value
ActiveCell.Offset(0, 2) = TextBox6.Value
ActiveCell.Offset(0, 3) = TextBox7.Value
ActiveCell.Offset(0, 4) = TextBox8.Value
ActiveCell.Offset(0, 5) = TextBox9.Value
ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 3) = UserForm1.ComboBox2.Value
ActiveCell.Offset(0, 4) = UserForm1.TextBox6.Value
ActiveCell.Offset(0, 5) = UserForm1.TextBox7.Value
ActiveCell.Offset(0, 6) = UserForm1.TextBox8.Value
Sheets("MAIN").Select
End Sub
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,108,839
Messages
5,525,160
Members
409,628
Latest member
inatecktechnology

This Week's Hot Topics

Top