Userform help

[rholdren]

This is my first attempt at building a Userform. It was actually going well and everything was working until I realized that one of my boxes was not posting a result. On the form I have a series of questions with Points awarded based on the answer. For example: It the answer to question 1 is worth 10 points the person will click on a OptionButton for 10 points and 10 will show in a TextBox next to it. This works all the way down the sheet for 13 questions but I couldn't get the boxes to add and populate in a TextBox named Your Total Points. Then it occurred to me that this is because they are all text boxes. I have tried variety of things to get it to add but the best I could get was if Q1 was 10 and Q2 was 5 Your Points were 105.

I did find a thread on here were someone had a similar problem but solved it themselves so I never really got to see what it was that they solved.

How do I get the each text boxes to display results as numeric? Any help would be greatly appreciated.

Thanks

 

[Fluff]

I'm an option explicit kinda guy lol

Me too.

 

[rholdren]

I recopied the code but switched
Me.TbYourTotal.Value = ttl
to
Me.CSRPoints.Value = ttl and I'm getting errors I get an error without switching and I get an error when I had TbCSRPoints
the error is different depending on who I change or (not change this line)

End Sub

 

[rholdren]

this line seems to be what is causing me the issue

Me.TbYourTotal.Value = ttl

Ive tried a combination of this replacing the YourTotal.Value = ttl with the actual textbox name (CSRPoints) leaving the Tb in and taking it out
and even if I don't change it I still get an error method or data member not found

If I take out the TB this line highlights with an error
If Me.Controls("Textbox" & i).Value = "" Then

 

[jproffer]

What are the names of your textboxes? At least please tell me they're named consecutively...IE, tb1, 2, 3 etc....or Points1, 2, 3, 4 etc....something

With the total one, change mine to yours...everything between the dots. So Replace TbYourPoints with whatever the entire name of yours is.

 

[rholdren]

They go points1, points2, points3 ...... points13 the box where the total shows is named CSRPoints.

I haven't been doing Excel for very long but I have picked up a couple of books but sometimes I just run into things that the books don't cover. Any thoughts on a good book?

 

[jproffer]

Private Sub TextBox13_Change()
Dim i As Long
Dim j As Long
Dim ttl As Double

ttl = 0

    For i = 1 To 13
        If Me.Controls("points" & i).Value = "" Then
            MsgBox "Please select a point value for question " & i
        End If
    Next i

    For j = 1 To 13
        ttl = ttl + Me.Controls("points" & j).Value
    Next j
    
    Me.CSRPoints.Value = ttl
    
End Sub

Try that. And I have no idea on books...I've never tried any of them. I learned most of what little I know by reading here

BTW, if you don't care if all 13 textboxes are filled, it would be this:

Private Sub TextBox13_Change()
Dim i As Long
Dim j As Long
Dim ttl As Double

ttl = 0

    For j = 1 To 13
        ttl = ttl + Me.Controls("Textbox" & j).Value
    Next j
    
    Me.TbYourTotal.Value = ttl
    
End Sub

Either way though, it will only happen when the 13th Textbox is filled in.

 

[Rick Rothstein]

They go points1, points2, points3 ...... points13 the box where the total shows is named CSRPoints.

I haven't been doing Excel for very long but I have picked up a couple of books but sometimes I just run into things that the books don't cover. Any thoughts on a good book?

Put this function in your UserForm code module and then call it and assign its output to the CSRPoints TextBox right after one of your Points# TextBoxes have been updated...

Function PointsTotal()
  Dim X As Long, Cntl As Control, Sum As Double
  For X = 1 To 13
    Sum = Sum + Val(Controls("Points" & X).Value)
  Next
  PointsTotal = Sum
End Function

 

[jproffer]

WAIT!!!



When you copy that, don't copy the first or last line. "Sub Textbox_Change" wont work for you.

Double click on your last textbox (#13), and paste everything (minus the 2 lines mentioned) between what comes up.

 

[rholdren]

Everything is working fine. I really appreciate it and want to say thanks for everything everyone did. You all are amazing.