none
Automatically Calculate the values in the text box using VBA RRS feed

  • Question

  • Hello 

    I have designed one form in VBA with 6 text boxes. I want to automatic calculation of total values of the text box which is entered by user. It means i want to set the values whose sum equal to 100. If it is exceed 100, it will automatically set to 100. Is it possible to give maximum and minimum limit to text box values.

    Please do help me.

    Thanks & Regards

    Rushali


    Saturday, January 30, 2016 7:14 AM

All replies

  • Re:  I have designed one form in VBA with 6 text boxes

    Is the form a UserForm or something on a worksheet?
    If using a worksheet, what type of textbox are you using?

    Do you want to check the sum for all six textboxes  or  find any individual box whose value > 100?

    '---
    Jim Cone
    Portland, Oregon USA

    • Edited by James Cone Monday, March 28, 2016 3:11 PM
    Saturday, January 30, 2016 5:09 PM
  • Hi,

    I've made a sample VBA.

    It works like the below:
      TextBox1 ~ 6 are on Worksheet. TextBox6 is a sum of TextBox1 ~ 5.
      When sum is greater than 100, BackColor of TextBox5 turns to Red,
      else turns to Green.
      

    You can download it from here(Excel_TextBox_Sum.xlsm).

    Regards.
    Sunday, January 31, 2016 1:36 AM
  • Hi rushuMT,

    I forgot to show my code in the previous post.
    It's here.
    Private Sub TextBox1_Change()
        ' --- requires numeric check
        Call prc_Sum
    End Sub
    Private Sub TextBox2_Change()
        ' --- requires numeric check
        Call prc_Sum
    End Sub
    Private Sub TextBox3_Change()
        ' --- requires numeric check
        Call prc_Sum
    End Sub
    Private Sub TextBox4_Change()
        ' --- requires numeric check
        Call prc_Sum
    End Sub
    Private Sub TextBox5_Change()
        ' --- requires numeric check
        Call prc_Sum
    End Sub
    ' ---
    Private Sub prc_Sum()
        If Len(TextBox1.Text) = 0 Then
            TextBox1.Value = 0
        End If
        If Len(TextBox2.Text) = 0 Then
            TextBox2.Value = 0
        End If
        If Len(TextBox3.Text) = 0 Then
            TextBox3.Value = 0
        End If
        If Len(TextBox4.Text) = 0 Then
            TextBox4.Value = 0
        End If
        If Len(TextBox5.Text) = 0 Then
            TextBox5.Value = 0
        End If
        ' ---
        Dim tempSum As Integer
        tempSum = CInt(TextBox1.Value) + CInt(TextBox2.Value) + CInt(TextBox3.Value) _
                        + CInt(TextBox4.Value) + CInt(TextBox5.Value)
        ' ---
        If tempSum > 100 Then
            TextBox6.Value = 100
            TextBox6.BackColor = RGB(255, 0, 0)
        Else
            TextBox6.Value = tempSum
            TextBox6.BackColor = RGB(0, 255, 0)
        End If
    End Sub
    
    Regards.
    Sunday, January 31, 2016 2:01 AM
  • Hi,

    I modified Excel file. It has TextBox on UserForm.
    Please download from here.  

    Regards.
    Sunday, January 31, 2016 4:36 AM
  • Hello Ashidacchi

    I have used user form not worksheet and i want to set the total values of text box should be 100. If it is exceed 100 then it will automatically reset the values.

    For reference,attached herewith screenshot of my user form.

    Thanks & Regards

    Rushali

    Wednesday, February 3, 2016 6:24 AM
  • Hi rushuMT,

    I'm grad to hear that. 
    Congratulations!
    Wednesday, February 3, 2016 6:30 AM