Excel Sub in Form doesnt seem to be working

[Seandobson2402]

Below is the code I have been working on for a form I have created for work. Basically to make sure nobody is taking the @##@ and targets are beings hit.

Private Sub bClear_Click()
   With Me
        
        .cbSeanProactive.Value = ""
        .cbSeanMailbox.Value = ""
        .cbSeanSalesforce.Value = ""
        .cbSeanForce.Value = ""
        .cbSeanHoliday.Value = ""
        .cbSeanOther.Value = ""
        .tSean.Value = ""
        
   End With
End Sub


Private Sub cbSeanDobson_Click()
Call PointsCalcLogic
End Sub


Private Sub tSean_Change()
End Sub


Private Sub UserForm_Initialize()
    With Me
        
        'intialise the form
        .cbSeanDobson.Value = False
        
        .cbSeanProactive.Value = ""
        .cbSeanMailbox.Value = ""
        .cbSeanSalesforce.Value = ""
        .cbSeanForce.Value = ""
        .cbSeanHoliday.Value = ""
        .cbSeanOther.Value = ""
  
        'fill dropdowns
        Call FillDropDownInteger(.cbSeanProactive, 0, 3000)
        Call FillDropDownInteger(.cbSeanMailbox, 0, 3000)
        Call FillDropDownInteger(.cbSeanSalesforce, 0, 3000)
        Call FillDropDownInteger(.cbSeanForce, 0, 3000)
        Call FillDropDownInteger(.cbSeanHoliday, 0, 3000)
        Call FillDropDownInteger(.cbSeanOther, 0, 3000)
  
        Call EnableChecksComplete(False)
        Call EnablePoints(False)
        
        Call PointsCalcLogic
     
    End With
    
End Sub


Private Sub PointsCalcLogic()
       
        If cbSeanDobson.Value = True Then
            Call EnableChecksComplete(True)
            Call EnablePoints(True)
            Call GetCalculatedPoints
        Else
            Call EnableChecksComplete(False)
            Call EnablePoints(False)
        End If
        
   

End Sub


Private Sub EnableChecksComplete(State As Boolean)
'enables or disable checks complete, boolean paramter passed to determine state
    With Me
        'set labels and dropdowns to required state
        .fProactive.Enabled = State
        .fTotalPoints.Enabled = State
        
        .cbSeanProactive.Enabled = State
        .cbSeanMailbox.Enabled = State
        .cbSeanSalesforce.Enabled = State
        .cbSeanForce.Enabled = State
        .cbSeanHoliday.Enabled = State
        .cbSeanOther.Enabled = State
        .tSean.Enabled = State
        
         
            'set colour coding and defaulst accoring to state
        If Not State Then
        
        .fProactive.BackColor = &H80000005
        .fTotalPoints.BackColor = &H80000005
        
        .cbSeanProactive.BackColor = &H80000005
        .cbSeanMailbox.BackColor = &H80000005
        .cbSeanSalesforce.BackColor = &H80000005
        .cbSeanForce.BackColor = &H80000005
        .cbSeanHoliday.BackColor = &H80000005
        .cbSeanOther.BackColor = &H80000005
        .tSean.BackColor = &H80000005
        
                
            'clear values
            
        
        
        .cbSeanProactive.Value = ""
        .cbSeanMailbox.Value = ""
        .cbSeanSalesforce.Value = ""
        .cbSeanForce.Value = ""
        .cbSeanHoliday.Value = ""
        .cbSeanOther.Value = ""
        .tSean.Value = ""
 
            
        Else
        
            'set checks complete to red
          
       
        .cbSeanProactive.BackColor = &HFF&
        .cbSeanMailbox.BackColor = &HFF&
        .cbSeanSalesforce.BackColor = &HFF&
        .cbSeanForce.BackColor = &HFF&
        .cbSeanHoliday.BackColor = &HFF&
        .cbSeanOther.BackColor = &HFF&
        '&HFF&
        
        
  
        End If
    End With
    
End Sub


Private Sub EnablePoints(State As Boolean)
'enables or disable points box, boolean paramter passed to determine state
    With Me
        'set labels and dropdowns to required state
        .fTotalPoints.Enabled = State
        .tSean.Enabled = State
        'set colour coding and defaulst accoring to state
        If Not State Then
            'set to off white
        .tSean.BackColor = &H80000005
           
            'clear values
        .tSean.Value = ""
            
        Else
            'set points to red
        .tSean.BackColor = &HFF&
            
        End If
    End With
    
End Sub


Private Sub GetCalculatedPoints()
    
    Dim AllChecksCompleteEntered As Long
    
    With Me
        'set the colours to red if not value not entered, off white otherwise
        If .cbSeanProactive.Value = "" Then .cbSeanProactive.BackColor = &HFF& Else .cbSeanProactive.BackColor = &H80000005
        If .cbSeanMailbox.Value = "" Then .cbSeanMailbox.BackColor = &HFF& Else .cbSeanMailbox.BackColor = &H80000005
        If .cbSeanSalesforce.Value = "" Then .cbSeanSalesforce.BackColor = &HFF& Else .cbSeanSalesforce.BackColor = &H80000005
        If .cbSeanForce.Value = "" Then .cbSeanForce.BackColor = &HFF& Else .cbSeanForce.BackColor = &H80000005
        If .cbSeanHoliday.Value = "" Then .cbSeanHoliday.BackColor = &HFF& Else .cbSeanHoliday.BackColor = &H80000005
        If .cbSeanOther.Value = "" Then .cbSeanOther.BackColor = &HFF& Else .cbSeanOther.BackColor = &H80000005
        
 
        'determine if all checks have been entered
        
        AllChecksCompleteEntered = .cbSeanProactive.Value <> "" And .cbSeanMailbox.Value <> "" And .cbSeanSalesforce.Value <> "" _
        And .cbSeanForce.Value <> "" And .cbSeanHoliday.Value <> "" And .cbSeanOther.Value <> ""
        
        
        If AllChecksCompleteEntered Then
            .tSean.BackColor = &H80000005
            .tSean.Value = Format( _
            ThisWorkbook.Worksheets("PointsTruth").Cells(3, "B").Value * .cbSeanProactive.Value + _
            ThisWorkbook.Worksheets("PointsTruth").Cells(4, "B").Value * .cbSeanMailbox.Value + _
            ThisWorkbook.Worksheets("PointsTruth").Cells(5, "B").Value * .cbSeanSalesforce.Value + _
            ThisWorkbook.Worksheets("PointsTruth").Cells(6, "B").Value * .cbSeanForce.Value + _
            ThisWorkbook.Worksheets("PointsTruth").Cells(7, "B").Value * .cbSeanHoliday.Value + _
            ThisWorkbook.Worksheets("PointsTruth").Cells(8, "B").Value * .cbSeanOther.Value _
            , "Number")
            
        Else
            .tSean.BackColor = &HFF&
            .tSean.Value = "N/A"
        End If
        
    End With
    
End Sub


Private Sub FillDropDownInteger(Target As Variant, StartVal As Integer, EndVal As Integer)
'fills target dropdown with integer values between startval and endval inclusive
    Dim Cnt As Integer
    For Cnt = StartVal To EndVal
        Target.AddItem Cnt
    Next Cnt
    
End Sub

The error seems to be with - Private Sub GetCalculatedPoints() - For some reason the values entered into the ComboBoxes are not being recognised. They do not change to white once input and my tSean (Total calculation) remains at N/A instead of being calculated based on the routine. Any ideas what it could be? I've been looking at this for hours and I can't seem to locate the problem in my code.

 

[gibra]

Need a file to make a DEBUG of your code.

 

[Seandobson2402]

Need a file to make a DEBUG of your code.

Does this link work mate? https://1drv.ms/x/s!AnDVx66mvRylgRmseLMlmRHH8XVG .. its a link to my Microsoft Excel file uploaded onto OneDrive

 

[gibra]

There is no code that calculate the values entered on ComboBoxes.
I don't know where you want to calculate them:
- on ComboBox Change/Click event?
- on TBC button click event?

 

[Seandobson2402]

There is no code that calculate the values entered on ComboBoxes.
I don't know where you want to calculate them:
- on ComboBox Change/Click event?
- on TBC button click event?

What about the If AllChecksCompleteEntered Then code that I have added calculations to - Multiplying the values entered into the combo boxes by the values of B3 - B8 on the PointsTruth tab?

I wants the values entered into the combo boxes to be multiplied by the values of PointsTruth B3 to B8 and then the total to be entered into tSean, which is the Total Points box.

 

[Seandobson2402]

Still stumped

 

[Seandobson2402]

Still can't figure out where I'm going wrong. I've continued to look at the code and it all looks fine in my eyes

 

[Seandobson2402]

Fixed - Stupid and simple. You're right, I needed to add my PointsCatLogic into the combo boxes change events.

 

[gibra]

What about the If AllChecksCompleteEntered Then code that I have added calculations to - Multiplying the values entered into the combo boxes by the values of B3 - B8 on the PointsTruth tab?

If you make a DEBUG of your code, you see that:

1) AllChecksCompleteEntered variabile will set from GetCalculatedPoints() called on PointsCalcLogic() routine.
2) The PointsCalcLogic() is called only in two place:
- UserForm_Initialize(), so here does nothing
- from cbSeanDobson_Click, but ONLY when cbSeanDobson.Value will set to True, this mean that you execute GetCalculatedPoints() always and only when comboboexs values are empty.

As a result, nothing happens.

You should call PointsCalcLogic somewhere after you set then Values of the ComboBoxes.

That's why I wrote to you that you should call PointsCalcLogic from the Click event of each combobox, or from the cbUpdateStats button.

EDIT:
Sorry. I had not read your post # 8