Excel Sub in Form doesnt seem to be working

Seandobson2402

New Member
Joined
Feb 9, 2018
Messages
23
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.
Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

gibra

New Member
Joined
Mar 22, 2018
Messages
11
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?
 
Last edited:

Seandobson2402

New Member
Joined
Feb 9, 2018
Messages
23

ADVERTISEMENT

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

New Member
Joined
Feb 9, 2018
Messages
23

ADVERTISEMENT

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

New Member
Joined
Feb 9, 2018
Messages
23
Fixed - Stupid and simple. You're right, I needed to add my PointsCatLogic into the combo boxes change events.
 

gibra

New Member
Joined
Mar 22, 2018
Messages
11
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top