View Full Version : VBA Form Opt. buttons scoring

03-31-2012, 03:39 PM

My question is a bit tricky.. i think

I'm trying to create a form/tool that when you select an option if gives you a score, based on a selection of a couple of different options. It sums the score - on the form.:banghead:

there are only two sections that require a score - so it will only need to sum two scores.

Also.. should i be using Opt. buttons or should i be using Combo boxes for the selection criteria?

Please help!!


03-31-2012, 11:48 PM
Greetings K,

I see that you just joined, welcome to vbaexpress :-)

I also noticed that you cross-posted Here (http://www.mrexcel.com/forum/showthread.php?t=625553).

Might I suggest reading this:
As to your question, it is a bit unclear to me where we are summing from. Rather than guessing, maybe it would be easier if you were to attach a small example workbook with what we want to do (in .xls format).

To post a sample, click the <Go Advanced> button beneath the Quick Reply box. In the new window, scroll down to <Manage Attachments>.


04-01-2012, 12:39 AM

oh thanks for that! I didn't know that about the cross-posting!

Its just that I'm in such a rush to get this problem sorted and have been searching online for 3 days now!

ohh yes here's the code.. please feel free to point out other flaws in my code - this is the first "big" tool i'm developing.

Option Explicit

'set variables

Dim strDirectorate As String

Dim strTask As String

Dim intPriority As Integer

Dim intBracket As Integer

Dim strUrgency As String

Dim strProjName As String

Dim strIfNt As String

Dim strGroup As String

Dim strPerson As String

Dim intFte1 As Integer

Dim intFte2 As Integer

Dim intVari As Integer

Dim datDate As Date

Public Sub cmdVari_Click()

lblVari = (txtFte1.Text) - (txtFte2.Text)

End Sub

Public Sub txtDa_enter()

MsgBox "Enter start and end date the following format: mm-yyyyy to mm-yyyy", vbCritical & vbOKOnly

'enters current date

txtDa = Format(txtDa, "mm-yyyy")

Range("Task_Start_Date") = Format(txtDa, "mm-yyyy")

End Sub

Public Sub UserForm_Initialize()

'populate Directorate combo box

cboDirectorate.AddItem "DAC"

cboDirectorate.AddItem "DP"

cboDirectorate.AddItem "DC"

cboDirectorate.AddItem "DF"

cboDirectorate.AddItem "DO"

Public Sub cmdAdd_Click()

Dim Outcome As String

Dim Urgency As String

'pass on variables

strDirectorate = cboDirectorate

strTask = txtTaskList

strProjName = cboProjName

strIfNt = txtIfNt

strGroup = cboGroup

strPerson = cboAsstPerson

intFte1 = txtFte1

intFte2 = txtFte2

intVari = lblVari

datDate = txtDa

'transfer Outcomes into worksheet

If Me.optContAnPla Then

Outcome = "Contributes to Plan"

ElseIf Me.optContMaj Then

Outcome = "Contributes to a M Project/Programme"

ElseIf Me.optContMinor Then

Outcome = "Contributes to a Mi Project"

ElseIf Me.optContUOR Then

Outcome = "Contributes to a U"

ElseIf Me.optContNone Then

Outcome = "Does not contribute to any of the above"

End If

'transfer Urgency into worksheet

If Me.optImpNoUr Then

Urgency = "Important - Not Urgent"

ElseIf Me.optNotImUr Then

Urgency = "Not Important - Urgent"

ElseIf Me.optNtImNtUr Then

Urgency = "Not Important - Not Urgent"

ElseIf Me.optUImUr Then

Urgency = "Important - Urgent"

End If

'Setting scores for Outcomes

If optContAnPla = True Then

lblScore = 4

ElseIf optContMaj = True Then

lblScore = 4

ElseIf optContMinor = True Then

lblScore = 3

ElseIf optContUOR = True Then

lblScore = 5

ElseIf optContNone = True Then

lblScore = 0

End If

'use loop to insert data into worksheet


'insert date into first cell

Do Until ActiveCell = Empty

ActiveCell.Offset(1, 0).Select


ActiveCell.Value = strDirectorate

ActiveCell.Offset(0, -1).Select

ActiveCell.Value = intPriority

ActiveCell.Offset(0, 2).Select

ActiveCell.Value = datDate

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = strTask

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Urgency

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Outcome

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = strProjName

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = strIfNt

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = strGroup

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = strPerson

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = intFte1

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = intFte2

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = intVari

'trying to pass value to total cost lable on form

lblScore = intPriority

End Sub

I need to give each option in the Outcomes and the Urgency opt. a score - which will show up on the form (on lblScore) and in the worksheet.

I hope you can help : pray2:


04-01-2012, 01:23 AM
Happy to help if I can, and glad you joined :-)

I see it is late in the day there, but I must leave for a bit. I'm not sure I'll ba able to stay awake, but if I do, I'll check back. With that many controls and such, it really would likely be better to post a trimmed down version in .xls format.

I think that all we'd need is the userform and any associated code, as well as the named range, so it would seem easy to obfuscate any sensitive/private data.