Modify vba calculation to include percentage when criteria is met

jbeet

New Member
Joined
Feb 1, 2015
Messages
13
I have a form which has several combo boxes with the options of “Yes”, “No” or “N/A”. Some of the cbo’s have special requirements. When “No” is select in a few of the cbo’s a form will pop up requesting some additional information resulting is a percentage.
When there is a percentage entered into this box, I need the below code to calculate the score incorporating the percentage instead of following the normal calculation for “No” responses.

  • The form uses a Tab control. Each tab has specific cbo’s with the TAG property set based on the tab. For example, the Documentation tab has 6 cbo’s which all have the Tag “Doc”
  • Each of the cbo’s are named:
    • DocQ1, DocQ2, DocQ3, DocQ4, DocQ5 and DocQ6
    • DocQ2, DocQ3 and DocQ5 each have the additional requirement if “No” is selected. So when calculating the score, if any of these 3 cbo’s = “No” then I need the code to use the variance score in the calculation which will be listed as DocQ2Var, DocQ3Var and/or DocQ5Var. Otherwise the code should calculate the “yes” and the “N/A” as it is currently written.
    • DeskFieldTable is the name of the table that both of the forms use as a record source.


The below code works great currently but I do not know how to modify it to incorporate the additional criteria of the variance score. Thank you in advance for any help you can provide!

VBA Code:
Private Sub btnDocScore_Click()
Dim c As Control, nYes As Long, nPartial As Long, nNo As Long, nNA As Long
nYes = 0
nPartial = 0
nNo = 0
nNA = 0


For Each c In Me.Controls
    If c.Tag = "Doc" Then
        If c.Value = "Yes" Then nYes = nYes + 1
        If c.Value = "Partially" Then nPartial = nPartial + 1
        If c.Value = "No" Then nNo = nNo + 1
        If c.Value = "NA" Then nNA = nNA + 1
    End If
Next c
DocScore = Format((nYes + nPartial * 0.5) / (nYes + nPartial + nNo + nNA), "Percent")

For Each c In Me.Controls
    If c.Tag = "Doc" Then
        If IsNull(c.Value) Then
         MsgBox ("ComboBox selection left blank. Please ensure all drop downs are selected before continuing.")
    Else
        txtDocStatus = "Complete"
    End If
End If
Next c

For Each c In Me.Controls
    If c.Tag = "Status" Then
        If c.Value = "Complete" Then
        txtDocStatus.BackColor = vbGreen
    End If
End If
Next c
End Sub

VBA Code:
Private Sub cboDocQ2_AfterUpdate()
Select Case Me.cboDocQ2
    Case "No"
        DoCmd.OpenForm "frm_Variance", acNormal, , "ID = " & txtMasterID
End Select

Me.Requery

End Sub
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,047
Office Version
  1. 365
Platform
  1. Windows
cross posted
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,323
Messages
5,624,014
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top