Results by GroupName

[netwerkz]

Hello all,

I have a userform for data entry purposes of gathering a student's name and selecting an elective class based on preference (1st choice, 2nd choice, 3rd choice). In my userform, I created 3 columns for each of the choices. All 3 columns contain the same list of classes. I decided to use optionbuttons to limit the entry to 1 option per column. I gave each column a GroupName, such as "electiveChoice1" and so on. Each option button refers to a different class and contains the GroupName as well as the optionbutton name G1_01, which right now stands for "Baking", G1_02" stands for Basket Weaving", etc. In each column, the optionbuttons are grouped in the same manner (G2_01, G3_01...both of which are also currently "Baking", but are considered 2nd and 3rd choice preferences). Most of what I have now, I did the pre-work in the properties window for each item.

I have cmdAdd and cmdClose buttons working just fine and have even setup the student's name to be entered into the next blank row of the "dataEntry" page. Now, I just need the value from each "GroupName" to be entered into the 2nd & 3rd column respectively of the "dataEntry" page.

Here's my code so far...

[B]Modules[/B]
Sub dataEntry_Click()
    frmRosterCreator.Show
End Sub



[B]Forms[/B]
Private Sub cmdAdd_Click()

    Dim RowCount As Long
    Dim fName As String
    Dim lName As String
    Dim electiveChoice1 As Long
    Dim electiveChoice2 As Long
    Dim electiveChoice3 As Long
        
    Dim ws As Worksheet
    Set ws = Worksheets("dataEntry")




    RowCount = dataEntered.Range("A1").CurrentRegion.Rows.Count + 1
    With ws
        .Cells(RowCount, 1).Value = Me.txtlName.Value
        .Cells(RowCount, 2).Value = Me.txtfName.Value
        .Cells(RowCount, 3).Value = Me.electiveChoice1.Value
        .Cells(RowCount, 4).Value = Me.electiveChoice2.Value
        .Cells(RowCount, 5).Value = Me.electiveChoice3.Value
    End With


'clear the data
Me.txtlName.Value = ""
Me.txtfName.Value = ""
Me.txtlName.SetFocus
End Sub
Private Sub cmdClose_Click()
  Unload Me
End Sub

Thank you for your quick response.
JD

 

[netwerkz]

Update...

Hello all,

I created a userform with (3) three groups of (10) ten OptionButtons.
Each group is named by GroupName as "electiveChoice1", "electiveChoice2", & "electiveChoice3".
Each OptionButton in each group is named as "electiveChoice1_01", "electiveChoice2", "electiveChoice3".

All (3) three groups contain the same electives like
electiveChoice1_01 = Baking
electiveChoice1_02 = Basket Weaving
electiveChoice1_03 = Being Handy

electiveChoice2_01 = Baking
electiveChoice2_02 = Basket Weaving
electiveChoice2_03 = Being Handy

electiveChoice3_01 = Baking
electiveChoice3_02 = Basket Weaving
electiveChoice3_03 = Being Handy

I have a "Submit this student and Add Another" button as well as a "Close Form" button, which clears and closes the form, both of which work just fine.

The student will make a elective choice by 1st, 2nd, and 3rd preference. That data will be entered into the userform, which will fill out the "dataEntry" page in a spreadsheet. The sheet contains (5) five columns, LName, FName, electivechoice1, electivechoice2, electivechoice3.

I need to have the value of the OptionButton selected placed into the spreadsheet for that GroupName for that student. Once the data entry is complete, I need the user to press a button to randomly select students fill an elective class from the given data, based by 1st, 2nd, and 3rd choice. No student can select the same class for each choice.

The userform looks great. Now, I need help in the code behind it!

This is what I have put together based on reading the forums...

Option ExplicitPrivate Sub cmdAdd_Click()


Dim RowCount As Long
Dim fName As String
Dim lName As String
Dim electiveChoice1 As Long
Dim electiveChoice2 As Long
Dim electiveChoice3 As Long
Dim objX As Object

Dim ws As Worksheet
Set ws = Worksheets("dataEntry")


Dim opt As MSforms.OptionButton


Set opt = GetSelectedOptionByGroupName("MyGroup")


If Not opt Is Nothing Then
MsgBox opt.Name
Else
MsgBox "No option selected"
End If

Function GetSelectedOptionByGroupName(strGroupName As String) As MSforms.OptionButton


Dim ctrl As Control
Dim opt As MSforms.OptionButton


'initialise
Set ctrl = Nothing
Set GetSelectedOptionByGroupName = Nothing


'loop controls looking for option button that is
'both true and part of input GroupName
For Each ctrl In Me.Controls
If TypeName(ctrl) = "OptionButton" Then
Set opt = ctrl
If opt.Value Then
Set GetSelectedOptionByGroupName = opt
Exit For
End If
End If
Next ctrl


End Function

RowCount = dataEntered.Range("A1").CurrentRegion.Rows.Count + 1
With ws
.Cells(RowCount, 1).Value = Me.txtlName.Value
.Cells(RowCount, 2).Value = Me.txtfName.Value

.Cells(RowCount, 3).Value = Me.opt.Value
.Cells(RowCount, 4).Value = Me.opt.Value
.Cells(RowCount, 5).Value = Me.opt.Value
End With


'clear the data
Me.txtlName.Value = ""
Me.txtfName.Value = ""
Me.txtlName.SetFocus
End Function

Private Sub cmdClose_Click()
Unload Me
End Sub




Please help.