PDA

View Full Version : [SOLVED] Total in user form



thombill
05-23-2005, 01:26 PM
I have created a user form that has ten text boxes. I want to add a 11th box that will display the sum of the first 10. The userForm populates a spreadsheet, so I could pull the sum off of the spreadsheet, but I can't figure out how to display the value of a cell that contains a formula.

Killian
05-23-2005, 01:34 PM
for, say, cell A14


Sub test()
'display the formula
MsgBox ActiveSheet.Range("A14").Formula
'display the value
MsgBox ActiveSheet.Range("A14").Value
End Sub

Bob Phillips
05-23-2005, 01:57 PM
I have created a user form that has ten text boxes. I want to add a 11th box that will display the sum of the first 10. The userForm populates a spreadsheet, so I could pull the sum off of the spreadsheet, but I can't figure out how to display the value of a cell that contains a formula.

I guess that you mean


Textbox11.Text = Range("A11").Text


Note that by using the Text property of the range you automatically pick up the format as well as the value.

Regouin
05-23-2005, 11:31 PM
easiest thing to do would be to create a sum on your worksheets and link that back to the textbox, as xld described. If you want to rule out the workbook you can do this.




textbox11.text = textbox1.text + textbox2.text + textbox3.text + textbox4.text + textbox5.text + textbox6.text + textbox7.text + textbox8.text + textbox9.text + textbox10.text


You might need to dim the text as long first.



dim i as long
dim j as long
etc.
i = textbox1.text
j = textbox2.text
etc
textbox11.text = i + j + etc


HTH

Frank

Bob Phillips
05-24-2005, 01:24 AM
textbox11.text = textbox1.text + textbox2.text + textbox3.text + textbox4.text + _
textbox5.text + textbox6.text + textbox7.text + textbox8.text + textbox9.text + textbox10.text






Dim ctl As Control
Dim ttl As Double
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Name <> "Textbox11" Then
If IsNumeric(ctl.Text) Then
ttl = ttl + CDbl(ctl.Text)
End If
End If
End If
Next ctl
TextBox11.Text = Format(ttl, "#,##0.00")

Regouin
05-24-2005, 01:33 AM
dim i as long
dim tot as long
i = 0
tot = 0
doi = i + 1
tot = tot + textbox & i & .text
loop until i = 10
textbox11.text = tot

this should work as well, I think

Norie
05-24-2005, 07:15 AM
Regouin

As far as I know that won't work.

You need to use code like xld posted to use that approach.

BlueCactus
05-24-2005, 07:22 AM
tot = tot + textbox & i & .text

I don't think this will work because VBA will look for a variable named textbox and an unspecified object with the property .Text

But if there is a way of programmatically assembling a statement like this, I'd be very interested to know.

Bob Phillips
05-24-2005, 07:51 AM
But if there is a way of programmatically assembling a statement like this, I'd be very interested to know.

Here is an interesting trick, add 11 textboxes and a commandbutton, put numbers in 1 to 10 and click.


Option Explicit

Dim colTextBoxes As New Collection

Private Sub CommandButton1_Click()
Dim ttl As Double
Dim i As Long
For i = 1 To colTextBoxes.Count
ttl = ttl + colTextBoxes(i).Text
Next i
TextBox11.Text = ttl
End Sub

Private Sub UserForm_Initialize()
colTextBoxes.Add Me.TextBox1
colTextBoxes.Add Me.TextBox2
colTextBoxes.Add Me.TextBox3
colTextBoxes.Add Me.TextBox4
colTextBoxes.Add Me.TextBox5
colTextBoxes.Add Me.TextBox6
colTextBoxes.Add Me.TextBox7
colTextBoxes.Add Me.TextBox8
colTextBoxes.Add Me.TextBox9
colTextBoxes.Add Me.TextBox10
End Sub

MOS MASTER
05-24-2005, 10:27 AM
IBut if there is a way of programmatically assembling a statement like this, I'd be very interested to know.
Hi, :yes

A little modification to Regouin sub's will work:


Private Sub CommandButton1_Click()
Dim i As Long
Dim tot As Double
i = 0
tot = 0
Do
i = i + 1
tot = tot + Me.Controls("TextBox" & i).Text
Loop Until i = 10
Me.TextBox11.Text = tot
End Sub


Enjoy! :whistle:

Regouin
05-24-2005, 11:07 PM
You have to address them as controls, like joost says

MOS MASTER
05-25-2005, 10:55 AM
Hi thombill, :yes

Did you find your answer yet? :whistle:

thombill
05-26-2005, 08:33 AM
I am working on trying the suggestions that have been mentioned above, thank you for all of the responses. Waiting on comments back from some peer review before going to much further. Thank you.:yes

thombill
05-27-2005, 08:44 AM
This solution works well for pulling the value off of the spreadsheet, however you have to select the textbox in the user form for it to update. Is there a way to have the textbox update every time a new value is added into the list. Maybe a goto or next statement using variables to cycle through the list???:yes


I guess that you mean


Textbox11.Text = Range("A11").Text


Note that by using the Text property of the range you automatically pick up the format as well as the value.

Norie
05-27-2005, 09:29 AM
Which solution are you referring to you where given quite a few I believe?

MOS MASTER
05-27-2005, 10:42 AM
This solution works well for pulling the value off of the spreadsheet, however you have to select the textbox in the user form for it to update. Is there a way to have the textbox update every time a new value is added into the list. Maybe a goto or next statement using variables to cycle through the list???:yes
Like Norie I'm having problems understanding at what action you want what (and where) to happen.

Could you provide us with your worksheet and tell us: If you do this I want that to happen in there?? :whistle:

stapuff
05-27-2005, 11:57 AM
thombill -

Giving you my take on what I think you are asking for. I am assuming you enter data (numbers) into Textbox 1 - 10 while Textbox 11 is giving you a "rolling" total after each Textbox entry.



Private Sub TextBox1_Change()
TextBox11.Text = val(TextBox1.Value)
End Sub
Private Sub TextBox2_Change()
TextBox11.Text = val(TextBox1.Value) + val(Textbox2.Value)
End Sub
Private Sub TextBox3_Change()
TextBox11.Text = val(TextBox1.Value) + val(Textbox2.Value) + val(Textbox3.Value)
End Sub
Private Sub TextBox4_Change()
TextBox11.Text = val(TextBox1.Value) + val(Textbox2.Value) + val(Textbox3.Value) + val(Textbox4.Value)
End Sub


You can see the idea behind it. I did not go all the way to Textbox10, but I thought I would give an example.

HTH

Kurt

Bob Phillips
05-27-2005, 12:09 PM
Giving you my take on what I think you are asking for. I am assuming you enter data (numbers) into Textbox 1 - 10 while Textbox 11 is giving you a "rolling" total after each Textbox entry.

You can see the idea behind it. I did not go all the way to Textbox10, but I thought I would give an example.

Would be better to create a pseudo-control array for the textboxes to stop the repetition, and handle all the same. You could then creat an input mask to trap invalid input. Nice and simple.

stapuff
05-27-2005, 12:27 PM
xld -

In this situation - how would you do that?

The post was written:
" I want to add a 11th box that will display the sum of the first 10. The userForm populates a spreadsheet"

What I understand from that is the UF will be used to collect data - then transfer to the spreadsheet. a1=TB1, a2=TB2,a3=TB3 through a10=TB10 as an example.
I am only assuming the user put the =sum(A1:A10) on the sheet because he did not know how to get the added TB(11) to sum.

I know the code I supplied was not pretty, but it works. Effective=TBD.
I posted for several reasons - to give a different perspective on what I thought was an answer to the question and learn how others would do it.

I am really intrested in "Would be better to create a pseudo-control array for the textboxes" since I use UF's a lot.

Thanks,

Kurt

Bob Phillips
05-27-2005, 01:51 PM
Hi Kurt,


What I understand from that is the UF will be used to collect data - then transfer to the spreadsheet. a1=TB1, a2=TB2,a3=TB3 through a10=TB10 as an example.
I am only assuming the user put the =sum(A1:A10) on the sheet because he did not know how to get the added TB(11) to sum.

I was not commenting on the worksheet bit, just suggesting an alternative to repetitive code.


I know the code I supplied was not pretty, but it works. Effective=TBD.
I posted for several reasons - to give a different perspective on what I thought was an answer to the question and learn how others would do it.

I was not commenting on your code, directly or indirectly, just pointing out that there are other ways.


I am really intrested in "Would be better to create a pseudo-control array for the textboxes" since I use UF's a lot.

The rub!

Okay, start off with VB, as against VBA. In VB, when you have many controls that act in the same way, you create a control array so that each control will trigger the same code. In this way, it reduces the effort in writing the code in the first place, just do it once, but as importantly in my view, it makes it easier to maintain. For instance, say you have a series of texboxes and you write some nice change code for them. All is working great until you decide to improve the input validation. If you have to code each separately, that is a chore, if you only have to code one control array event, life is easier.

As I said, VBA doesn't have control arrays, but you can simulate them. To do this you createa a class with a WithEvents public variable of the control type you want to manage. When you initiate the userform, for each of the controls that you want to behave the same you create an instance of the class, link the control to that class, and add it to a collection.

As an example, create a userform, add 5 commandbuttons to it, and change the captions of those buttons.

Then create a class, call it clsUserformEvents, and add this code


Option Explicit

Public WithEvents mButtonGroup As msforms.CommandButton

Private Sub mButtonGroup_Click()
MsgBox mButtonGroup.Caption & " has been pressed"
End Sub


Then if the form code module, add this code



Option Explicit

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cBtnEvents As clsUserFormEvents
Dim ctl As msforms.Control
Set mcolEvents = New Collection
For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
Set cBtnEvents = New clsUserFormEvents
Set cBtnEvents.mButtonGroup = ctl
mcolEvents.Add cBtnEvents
End If
Next
End Sub


Now show the form and click any of the buttons. You should get the idea.

The same technique can be applied to many controls, many events of the controls.

The major downside of this technique is that not all events are exposed, for instance the Textbox exit event.

That's about it. You can do the same with worksheet forms controls, worksheet ActiveX controls.

Hope that all makes sense.

stapuff
05-31-2005, 09:22 AM
xld -

Thanks for the post back and explaination. I did not feel you were busting my chops. I know probably 1% of what you do if even that - so - my post was an attempt to help explain what thombill was looking for (from my take on the question) more than solve the problem.

I have never used the WithEvents so I now have a new "toy" to play with. As I have mentioned - I do a lot of UF applications.

Again - I appreciate your post back and I will try your suggestion(s).

Thanks,

Kurt

Bob Phillips
05-31-2005, 10:10 AM
Hi again Kurt,


I did not feel you were busting my chops.

That wasn't how I meant to come across, I wanted to be clear that I wasn't criticsing you whilst sharing something that I knew :thumb.

I started the post with 'Hi Kurt', which is my way of trying to show friendliness - watch out when I don't :devil: (joking).


Again - I appreciate your post back and I will try your suggestion(s).

Your a gracious man Kurt, and I hope that my mumblings were helpful.

BTW, you can extend the technique to worksheet controls, ActiveX and forms. All good fun!

johnske
05-31-2005, 02:23 PM
This solution works well for pulling the value off of the spreadsheet, however you have to select the textbox in the user form for it to update. Is there a way to have the textbox update every time a new value is added into the list. Maybe a goto or next statement using variables to cycle through the list???

Hi, I must admit I haven't been following this thread too closely...

1) I wouldn't use the Initialize event, I'd use the Activate event

2) It seems what you'd need then (for updating) is a Worksheet event such as Worksheet_Calculate or worksheet_Change to trigger the Userform_Activate, such as


Private Sub Worksheet_Change(ByVal Target As Range)
UserForm2.Hide
UserForm2.Show False
End Sub

Or even


Private Sub Worksheet_Change(ByVal Target As Range)
Unload UserForm2
UserForm2.Show False
End Sub

HTH,
John