Userform not functioning properly

Question

When I execute my code on my user form, it sometimes shows the result and other times it doesn't.

What could be causing that problem?

Like, when I hit execute it'll give me an output, if I do it again with the same parameters it won't.

I don't know what's going on.

Any and all help is appreciated.

Thank you in advanced.

Here is a dropbox with my excel sheet:

Click here to go to dropbox

Here is my code:

Private Sub CommandButton2_Click() Me.Hide End Sub

Private Sub excecute_button_Click()

'Declaring objects 
 Dim N As Long, i As Long, subjectCount As Long 
 Dim tmp_avg As Double 
 Dim student As Range 
 Dim reading As Range, writing As Range, grammar As Range, spelling As Range 
 Dim math As Range, science As Range, social As Range 
 Dim average As Range 
 Dim info As Worksheet 
 Dim cutoff As String 
 Dim cutoff_score As Double 
 Dim output As String

'Setting objects 
 Set info = Worksheets("Info") 
 Set student = Range(info.Cells(6, 3), info.Cells(55, 3)) 
 Set reading = Range(info.Cells(6, 5), info.Cells(55, 5)) 
 Set writing = Range(info.Cells(6, 6), info.Cells(55, 6)) 
 Set grammar = Range(info.Cells(6, 7), info.Cells(55, 7)) 
 Set spelling = Range(info.Cells(6, 8), info.Cells(55, 8)) 
 Set math = Range(info.Cells(6, 9), info.Cells(55, 9)) 
 Set science = Range(info.Cells(6, 10), info.Cells(55, 10)) 
 Set social = Range(info.Cells(6, 11), info.Cells(55, 11)) 
 Set average = Range(info.Cells(6, 13), info.Cells(55, 13))

'Counting subjects 
        subjectCount = Me.readingBox.Value + _
        Me.writingBox.Value + _
        Me.grammarBox.Value + _
        Me.spellingBox.Value + _
        Me.mathBox.Value + _
        Me.scienceBox.Value + _
        Me.socialBox.Value

'Reading cut-off cutoff = Me.cutoff_box.Value

N = Worksheets("Info").Range("S19").Value i = 1

Do While i < N

    'Computing average
    tmp_avg = (reading.Cells(i, 1) * Me.readingBox.Value + _
        writing.Cells(i, 1) * Me.writingBox.Value + _
        grammar.Cells(i, 1) * Me.grammarBox.Value + _
        spelling.Cells(i, 1) * Me.spellingBox.Value + _
        math.Cells(i, 1) * Me.mathBox.Value + _
        science.Cells(i, 1) * Me.scienceBox.Value + _
        social.Cells(i, 1) * Me.socialBox.Value) / subjectCount

    'Rounding
    If Me.Round.Value = True Then
        average.Cells(i, 1).Value = WorksheetFunction.Ceiling(tmp_avg, 0.01)
    Else
        average.Cells(i, 1).Value = tmp_avg
    End If

    i = i + 1

    'Checking whether student met honor roll requirements
    Select Case cutoff
        Case "A+"
            cutoff_score = 0.96
        Case "A"
            cutoff_score = 0.93
        Case "A-"
            cutoff_score = 0.9
        Case "B+"
            cutoff_score = 0.86
        Case "B"
            cutoff_score = 0.83
        Case "B-"
            cutoff_score = 0.8
        Case "C+"
            cutoff_score = 0.76
        Case "C"
            cutoff_score = 0.73
        Case "C-"
            cutoff_score = 0.7
    End Select

    If average.Cells(i, 1).Value >= cutoff_score Then
        output = output & student.Cells(i, 1).Value & " "
    End If
         Loop

MsgBox "HONOR ROLL" & vbNewLine & output


End Sub

Private Sub UserForm_Click()

End Sub

Show Button Code:

Sub honor_roll_button()

With honor_roll_form

    'Loading combo box
    .cutoff_box.Clear
    .cutoff_box.AddItem "A+"
    .cutoff_box.AddItem "A"
    .cutoff_box.AddItem "A-"
    .cutoff_box.AddItem "B+"
    .cutoff_box.AddItem "B"
    .cutoff_box.AddItem "B-"
    .cutoff_box.AddItem "C+"
    .cutoff_box.AddItem "C"
    .cutoff_box.AddItem "C-"

    .cutoff_box.Value = "B+"

    'Setting default check boxes
    honor_roll_form.readingBox = True
    honor_roll_form.writingBox = True
    honor_roll_form.grammarBox = True
    honor_roll_form.spellingBox = True
    honor_roll_form.mathBox = True
    honor_roll_form.scienceBox = True
    honor_roll_form.socialBox = True
    honor_roll_form.Round = True

    'Showing Form
    .Show

End With


End Sub

Sub test()
Dim x As String
x = "hello"
x = x & vbNewLine & "goodbye"
MsgBox x
End Sub

when it doesnt, where and how does it stop? did you follow it up with the debugger by putting some breakpoint? Please provide more details. — A.S.H
– A.S.H, Commented Dec 3, 2016 at 18:56
@A.S.H Well, the code runs fine; it's just that - when I hit execute (upon opening up the user form: so I'm keeping all the default parameters selected) it'll display an output in the message box (the ID number). However, if I were to close the message box and do the same action again it will only show 'HONOR ROLL' and no ID when I had just did it 2 secs ago. The same is when I select and deselect some; when I hit do not round grade up etc. It'll show the ID once and not show it again or not show anything at all. — TommyBoy116
– TommyBoy116, Commented Dec 3, 2016 at 19:03
A couple of general programming comments, but will not fix this issue: (1) Your 'Do While i < N' should be <= N or else use a 'For i = 1 to N' You are only processing 49. With a 'For' you can remove the i = i + 1; (2) Your 'Select Case cutoff' should be moved before your 'Do Loop' because it is static for any loop and just wastes cycles. I've not seen any obvious solution yet, but will look further. — Wayne G. Dunn
– Wayne G. Dunn, Commented Dec 3, 2016 at 19:23
There are some statements that appear commented out or merged with previous statements in your code, but this is very likely and cut-paste problem, but I cant make sure. Would you please edit the post to make it correct. (i.e. lines cutoff = Me.cutoff_box.Value and N = Worksheets("Info").Range("S19").Value i = 1 ) — A.S.H
– A.S.H, Commented Dec 3, 2016 at 19:24

Wayne G. Dunn · Accepted Answer · 2016-12-04 00:01:32Z

3

Please make a backup copy of your workbook, then replace all your code in Honor Roll Form with the following and see if you still have the problem. I made a couple changes, now can't get it to fail.

NOTE I removed my 'For i = 1 to N' loop and returned to a modified version of your 'Do While i < N' loop. You need to use 'Do While i <= N' because your code never processed the last item. I also moved the 'i = i + 1' code to be the last instruction inside the 'Do' loop. By you having it in the middle of the loop, you always skipped the first 'If average.Cells...' code.

Although I moved the code I mentioned earlier out side the loop, it was working before I did that. And, not related, but I renamed your variables so I would know what type I was using - but that was not a solution either.

Option Explicit

Private Sub CommandButton2_Click()
    Me.Hide
End Sub

Private Sub excecute_button_Click()

'Declaring objects
Dim N As Long, i As Long, lSubjectCount As Long
Dim dTmp_Avg    As Double
Dim rngStudent  As Range
Dim rngReading  As Range, rngWriting As Range, rngGrammar As Range, rngSpelling As Range
Dim rngMath     As Range, rngScience As Range, rngSocial As Range
Dim rngAverage  As Range
Dim wsInfo      As Worksheet
Dim sCutoff     As String
Dim dCutoff_score As Double
Dim strOutput   As String

    'Setting objects
    Set wsInfo = Worksheets("Info")
    Set rngStudent = Range(wsInfo.Cells(6, 3), wsInfo.Cells(55, 3))
    Set rngReading = Range(wsInfo.Cells(6, 5), wsInfo.Cells(55, 5))
    Set rngWriting = Range(wsInfo.Cells(6, 6), wsInfo.Cells(55, 6))
    Set rngGrammar = Range(wsInfo.Cells(6, 7), wsInfo.Cells(55, 7))
    Set rngSpelling = Range(wsInfo.Cells(6, 8), wsInfo.Cells(55, 8))
    Set rngMath = Range(wsInfo.Cells(6, 9), wsInfo.Cells(55, 9))
    Set rngScience = Range(wsInfo.Cells(6, 10), wsInfo.Cells(55, 10))
    Set rngSocial = Range(wsInfo.Cells(6, 11), wsInfo.Cells(55, 11))
    Set rngAverage = Range(wsInfo.Cells(6, 13), wsInfo.Cells(55, 13))

    'Counting subjects
    lSubjectCount = Me.readingBox.Value + _
            Me.writingBox.Value + _
            Me.grammarBox.Value + _
            Me.spellingBox.Value + _
            Me.mathBox.Value + _
            Me.scienceBox.Value + _
            Me.socialBox.Value

    'Reading cut-off
    sCutoff = Me.cutoff_box.Value

    'Checking whether student met honor roll requirements               '### Move before your loop - no need to do this 50 times.
    Select Case sCutoff
        Case "A+"
            dCutoff_score = 0.96
        Case "A"
            dCutoff_score = 0.93
        Case "A-"
            dCutoff_score = 0.9
        Case "B+"
            dCutoff_score = 0.86
        Case "B"
            dCutoff_score = 0.83
        Case "B-"
            dCutoff_score = 0.8
        Case "C+"
            dCutoff_score = 0.76
        Case "C"
            dCutoff_score = 0.73
        Case "C-"
            dCutoff_score = 0.7
    End Select

    N = Worksheets("Info").Range("S19").Value
    i = 1

    Do While i <= N                        
        'Computing average
        dTmp_Avg = (rngReading.Cells(i, 1) * Me.readingBox.Value + _
            rngWriting.Cells(i, 1) * Me.writingBox.Value + _
            rngGrammar.Cells(i, 1) * Me.grammarBox.Value + _
            rngSpelling.Cells(i, 1) * Me.spellingBox.Value + _
            rngMath.Cells(i, 1) * Me.mathBox.Value + _
            rngScience.Cells(i, 1) * Me.scienceBox.Value + _
            rngSocial.Cells(i, 1) * Me.socialBox.Value) / lSubjectCount

        'Rounding
        If Me.Round.Value = True Then
            rngAverage.Cells(i, 1).Value = WorksheetFunction.Ceiling(dTmp_Avg, 0.01)
        Else
            rngAverage.Cells(i, 1).Value = dTmp_Avg
        End If

        If rngAverage.Cells(i, 1).Value >= dCutoff_score Then
            'Debug.Print "+++" & rngStudent.Cells(i, 1).Value & vbTab & rngAverage.Cells(i, 1).Value
            strOutput = strOutput & rngStudent.Cells(i, 1).Value & " "
        Else
            'Debug.Print "---" & rngStudent.Cells(i, 1).Value & vbTab & rngAverage.Cells(i, 1).Value
        End If

        i = i + 1
    Loop

    MsgBox "HONOR ROLL" & vbNewLine & strOutput


End Sub

Private Sub UserForm_Click()

End Sub

edited Dec 4, 2016 at 0:01

answered Dec 3, 2016 at 20:31

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TommyBoy116 Over a year ago

the only reason I had the do while was because one of my requirements was that I should have it. Thus, that is why I did what I did. If you have any suggestions on how I could implement a do while within my code without disrupting the integrity of it; which, it is obvious I did, I would appreciate that as well. Thank you.

TommyBoy116 Over a year ago

So place 'i = i + 1' above N = worksheets("info") etc.. and then de-comment the do while? Thanks again.

TommyBoy116 Over a year ago

Thank you; I think because I keep hitting the execute button it is taking some time to loop through my code and then output a result. Because when I do it twice over I get the intended result that I had when I first did it. And also, when I do the default execute, and then remove some subjects, if I hit execute it'll show the default output - but once I hit execute again it'll show a new output. I'm sure it has to do with my loop. In any case, thank you for your help.

TommyBoy116 Over a year ago

It works when I did your code; but because I needed the do loop without destroying the integrity of what I already have. The requirements I need to meet are have one do while or do until, select case, and an if then statement. I noticed that everytime I hit execute it automatically updates the percentages within the average column in the sheet. Which is what I believe to be is the culprit behind why certain times certain numbers are showing; etc. With this understanding, maybe you can show me something that'll correctly hit all those credentials as well as prevent the percents from updating.

Wayne G. Dunn Over a year ago

I updated the code to put back your 'Do While Loop'. You need to leave the code that recalc's the average column because if you choose a different combo of subjects, it would be incorrect. You can manually type 99% into the average column and watch as it corrects it when you run the code.

|

Collectives™ on Stack Overflow

Userform not functioning properly

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related