vba userform how do i get it to show calculations

shumidrives

New Member
Joined
Nov 9, 2016
Messages
10
so im wanting to create a user form that has 7 boxes i put numbers in 6 of the boxs and in the 7th it adds them all up and shows me the answer then it will also populate the excel sheet



is it even possible?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
Hi

You write in your title you want it to show calculations.
But in your description you suggest you simple want the answer in your 7th box.

Assuming you simply want the answer, this is how i would do it, but others may suggest another way

I would do this with 6 textboxes and do the calculations in a label.
Then write a macro that adds them up and call it on each change event, or have a command button to finally add them up and fire the answer into excel.

I ma happy to help you with this.
Let me know what direction you wish to go.

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
The following example code uses 7 textboxes and may do what you require

Dave

Code:
Private Sub TXTB1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB1 to your textbox name
Call ADDUP
End Sub
Private Sub TXTB2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB2 to your textbox name
Call ADDUP
End Sub
Private Sub TXTB3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB3 to your textbox name
Call ADDUP
End Sub
Private Sub TXTB4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB4 to your textbox name
Call ADDUP
End Sub
Private Sub TXTB5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB5 to your textbox name
Call ADDUP
End Sub
Private Sub TXTB6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'change TXTB6 to your textbox name
Call ADDUP
End Sub
Private Sub TOTAL1_Change() 'change TOTAL1 to your textbox name
Call ADDUP
With Sheets("sheet1") ' change sheet1 to your sheet name
.Range("A1") = TOTAL1 ' change A1 to the range you want the total in the sheet
End With
End Sub
Sub ADDUP()
Dim num(1 To 6) As Integer
num(1) = TXTB1 'change TXTB1 to your textbox name
num(2) = TXTB2 'change TXTB2 to your textbox name
num(3) = TXTB3 'change TXTB3 to your textbox name
num(4) = TXTB4 'change TXTB4 to your textbox name
num(5) = TXTB5 'change TXTB5 to your textbox name
num(6) = TXTB6 'change TXTB6 to your textbox name
TOTAL1 = num(1) + num(2) + num(3) + num(4) + num(5) + num(6) 'change TOTAL1 to your textbox name
End Sub
Private Sub UserForm_Initialize()
TXTB1 = 0: TXTB2 = 0: TXTB3 = 0: TXTB4 = 0: TXTB5 = 0: TXTB6 = 0 'change TB1 to TB6 to your textbox names
TOTAL1.Enabled = False ' change TOTAL1 to you text box name
End Sub
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
This example gives uses a command button and 7 text boxes.
when you hit the button it will calculate your answer and display it, but also put the answer into your sheet.

Code:
Private Sub CMD1_Click() 'change to your command button name
Dim num(1 To 6) As Integer
num(1) = TXTB1 'change TXTB1 to your textbox name
num(2) = TXTB2 'change TXTB2 to your textbox name
num(3) = TXTB3 'change TXTB3 to your textbox name
num(4) = TXTB4 'change TXTB4 to your textbox name
num(5) = TXTB5 'change TXTB5 to your textbox name
num(6) = TXTB6 'change TXTB6 to your textbox name
TOTAL1 = num(1) + num(2) + num(3) + num(4) + num(5) + num(6) 'change TOTAL1 to your textbox
With Sheets("sheet1") ' change sheet1 to your sheet name
.Range("A1") = TOTAL1 ' change A1 to the range you want the total in the sheet and TOTAL1 to yout textbox name
End With
End Sub
Private Sub UserForm_Initialize()
TOTAL1.Enabled = False ' change TOTAL1 to you text box name
End Sub

dave
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

If you have a UserForm with 7 Textboxes and your textbox are name Textbox1 Textbox2 etc.

This script will add up all the textbox values and enter the result in Textbox7

What you want after that is unknown to me.

Code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim ans As Long
ans = 0
    For i = 1 To 6
        ans = Me.Controls("Textbox" & i).Value + ans
    Next
TextBox7.Value = ans
End Sub
 

shumidrives

New Member
Joined
Nov 9, 2016
Messages
10
right so ive currently been working on it and currently got this code at the moment sure theres better ways to do this but i know how this works and under stand it only problem is now can i make it so if someone enters a M i want it to make the txtarrow = 0 and if someone enters an X i want it to make the txtarrow = 10

Code:
Private Sub Calculatebtn_click()On Error Resume Next
TextBox3 = (txtarrow1 + 0) + (txtarrow2 + 0) + (txtarrow3 + 0) + (txtarrow4 + 0) + (txtarrow5 + 0) + (txtarrow6 + 0)
TextBox4 = (txtarrow7 + 0) + (txtarrow8 + 0) + (txtarrow9 + 0) + (txtarrow10 + 0) + (txtarrow11 + 0) + (txtarrow12 + 0)
end sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Use Val() to convert you textbox strings into numeric values.

For the X deal, what other roman numerals where you going to allow? That could get messy. I guess you can Replace() after a lost focus or KeyDown, KeyUp, or KeyPress event for each textbox control.
 

shumidrives

New Member
Joined
Nov 9, 2016
Messages
10
Use Val() to convert you textbox strings into numeric values.

For the X deal, what other roman numerals where you going to allow? That could get messy. I guess you can Replace() after a lost focus or KeyDown, KeyUp, or KeyPress event for each textbox control.

all im wanting to do is make it so x = 10 and m = 0 thats the only 2texts i want to convert and i still want 1=1 2 =2 3=3 etc
also ive currently got this working for the x i just need to know how to make it work for the m aswell now
Code:
end1 = (IIf(Me.txtarrow1 = "x", 10, Me.txtarrow1) + 0)
 
Last edited:

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,177
Office Version
  1. 365
Platform
  1. Windows
A marriage of the methods posted:
Code:
Private Sub Calculatebtn_click()
  SumTBs TextBox3, 1, 6
  SumTBs TextBox4, 7, 12
End Sub

Private Sub SumTBs(tb As msforms.TextBox, iFrom%, iTo%, _
  Optional tbPrefix$ = "txtarrow")
  Dim d#, i%, tbs As msforms.TextBox
  For i = iFrom To iTo
    Set tbs = Controls(tbPrefix & i)
    d = d + Val(IIf(Len(tbs.Value) = 1, _
      Replace(LCase(tbs.Value), "x", "10"), tbs.Value))
  Next i
  tb = d
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,326
Messages
5,641,539
Members
417,217
Latest member
nextlove

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top