Results of OptionButtons by GroupName

[netwerkz]

20257


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.

[Bob Phillips]

Can you post the workbook?

[netwerkz]

Can you post the workbook?

Workbook uploaded in beginning of post.

[netwerkz]

Here, I'm exploring other methods of getting the value of the selected GroupName into the "dataEntry" spreadsheet into it's respective column (GroupName=electiveChoice1 into column 3, electiveChoice1 into column 4, & electiveChoice1 into column 5).



Option Explicit
Private Sub cmdAdd_Click()


Dim RowCount As Long
Dim fName As String
Dim lName As String


Dim electiveChoice1 As MSforms.OptionButton
Dim electiveChoice2 As MSforms.OptionButton
Dim electiveChoice3 As MSforms.OptionButton

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

If electiveChoice1 <> "" Then
If electiveChoice1 = "electiveChoice1_01" Then Set electiveChoice1 = "Baking"
Else
If electiveChoice1 = "electiveChoice1_02" Then Set electiveChoice1 = "Basket Weaving"
Else
If electiveChoice1 = "electiveChoice1_03" Then Set electiveChoice1 = "Being Handy"
Else
If electiveChoice1 = "electiveChoice1_04" Then Set electiveChoice1 = "Computer Lab"
Else
If electiveChoice1 = "electiveChoice1_05" Then Set electiveChoice1 = "Cooking"
Else
If electiveChoice1 = "electiveChoice1_06" Then Set electiveChoice1 = "Intro to Excel"
Else
If electiveChoice1 = "electiveChoice1_07" Then Set electiveChoice1 = "Library"
Else
If electiveChoice1 = "electiveChoice1_08" Then Set electiveChoice1 = "Reading"
Else
If electiveChoice1 = "electiveChoice1_09" Then Set electiveChoice1 = "Skateboarding"
Else
If electiveChoice1 = "electiveChoice1_10" Then Set electiveChoice1 = "Other"
End If
End If

If electiveChoice2 <> "" Then
If electiveChoice2 = "electiveChoice2_01" Then Set electiveChoice2 = "Baking"
Else
If electiveChoice2 = "electiveChoice2_02" Then Set electiveChoice2 = "Basket Weaving"
Else
If electiveChoice2 = "electiveChoice2_03" Then Set electiveChoice2 = "Being Handy"
Else
If electiveChoice2 = "electiveChoice2_04" Then Set electiveChoice2 = "Computer Lab"
Else
If electiveChoice2 = "electiveChoice2_05" Then Set electiveChoice2 = "Cooking"
Else
If electiveChoice2 = "electiveChoice2_06" Then Set electiveChoice2 = "Intro to Excel"
Else
If electiveChoice2 = "electiveChoice2_07" Then Set electiveChoice2 = "Library"
Else
If electiveChoice2 = "electiveChoice2_08" Then Set electiveChoice2 = "Reading"
Else
If electiveChoice2 = "electiveChoice2_09" Then Set electiveChoice2 = "Skateboarding"
Else
If electiveChoice2 = "electiveChoice2_10" Then Set electiveChoice2 = "Other"
End If
End If

If electiveChoice3 <> "" Then
If electiveChoice3 = "electiveChoice3_01" Then Set electiveChoice3 = "Baking"
Else
If electiveChoice3 = "electiveChoice3_02" Then Set electiveChoice3 = "Basket Weaving"
Else
If electiveChoice3 = "electiveChoice3_03" Then Set electiveChoice3 = "Being Handy"
Else
If electiveChoice3 = "electiveChoice3_04" Then Set electiveChoice3 = "Computer Lab"
Else
If electiveChoice3 = "electiveChoice3_05" Then Set electiveChoice3 = "Cooking"
Else
If electiveChoice3 = "electiveChoice3_06" Then Set electiveChoice3 = "Intro to Excel"
Else
If electiveChoice3 = "electiveChoice3_07" Then Set electiveChoice3 = "Library"
Else
If electiveChoice3 = "electiveChoice3_08" Then Set electiveChoice3 = "Reading"
Else
If electiveChoice3 = "electiveChoice3_09" Then Set electiveChoice3 = "Skateboarding"
Else
If electiveChoice3 = "electiveChoice3_10" Then Set electiveChoice3 = "Other"
End If
End If

RowCount = ws.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


It does not work, so I still need help....PLEASE!!!

[snb]

I'd use:


Private Sub cmdAdd_Click()
ReDim sn(0, 4)

sn(0, 0) = txtfName
sn(0, 1) = txtlName

For Each it In Controls
If TypeName(it) = "OptionButton" Then
If it Then sn(0, Right(it.GroupName, 1) + 1) = it.Caption
End If
Next

Sheets("dataentry").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 5) = sn
End Sub

[Paul_Hossler]

Suggestion -- use listboxes instead of all those radio buttons -- much easier to deal with



20262

Your UserForm code is much simpler --



Option Explicit
Private Sub UserForm_Initialize()
Me.ec1.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
Me.ec2.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
Me.ec3.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
Me.ec1.ListIndex = -1
Me.ec2.ListIndex = -1
Me.ec3.ListIndex = -1
End Sub

Private Sub cmdAdd_Click()
Dim rowNext As Long

With Worksheets("dataEntry")
rowNext = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

.Cells(rowNext, 1).Value = Me.txtlName.Text
.Cells(rowNext, 2).Value = Me.txtfName.Text
If Me.ec1.ListIndex > -1 Then .Cells(rowNext, 3).Value = ec1.List(ec1.ListIndex)
If Me.ec2.ListIndex > -1 Then .Cells(rowNext, 4).Value = ec2.List(ec2.ListIndex)
If Me.ec3.ListIndex > -1 Then .Cells(rowNext, 5).Value = ec3.List(ec3.ListIndex)
End With
'clear
Me.txtlName.Text = vbNullString
Me.txtfName.Text = vbNullString
Me.ec1.ListIndex = -1
Me.ec2.ListIndex = -1
Me.ec3.ListIndex = -1
End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub

[snb]

@Paul

Instead of

Me.ec1.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
Me.ec2.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
Me.ec3.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")

easier to deal with:


ec1.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
ec2.List = ec1.List
ec3.List = ec1.List
NB. The listindices are always -1 if you assign a new list to a combobox/listbox


easier to deal with:

Private Sub cmdAdd_Click()
sheets("dataEntry").Cells(Rows.Count, 1).End(xlUp).offset(1).resize(,5)=array(txtlName,txtfName,ec1,ec2,ec3)

for each it in controls
if typename(it)="TextBox" then it=""
if typename(it)="ComboBox" then it.listindex=-1
next
End Sub

[SamT]

Here's an example of another way. You'll have to run Form1 from VBA. Note that Frames provide Exclusive OptionButtons and allow iterating thru their controls. I didn't use Labels in the Frames so I didn't have to code around them.

It's much longer tjhan the others, but IMO easier to maintain and modify and by saving the the selections to variables, provides for usage elsewhere.


Option Explicit

Dim ElecChoice1 As String
Dim ElecChoice2 As String
Dim ElecChoice3 As String


Private Sub cmdAdd_Click()

MsgBox "Choice#1 is " & ElecChoice1 & vbCrLf _
& "Choice#2 is " & ElecChoice2 & vbCrLf _
& "Choice#3 is " & ElecChoice3

End Sub


Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

GetOption Me.Frame1

DisableOptions Me.Frame2
DisableOptions Me.Frame3

End Sub

Private Sub Frame2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

GetOption Me.Frame2

DisableOptions Me.Frame1
DisableOptions Me.Frame3

End Sub

Private Sub Frame3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

GetOption Me.Frame3

DisableOptions Me.Frame1
DisableOptions Me.Frame2

End Sub



Private Sub DisableOptions(Frm As MSForms.Frame)
Dim i As Long
With Frm
For i = 0 To .Controls.Count - 1
With .Controls(i)
If Not .Value = True Then
If .Caption = ElecChoice1 _
Or .Caption = ElecChoice2 _
Or .Caption = ElecChoice3 _
Then
.Enabled = False
Else: .Enabled = True
End If
End If
End With
Next
End With

End Sub


Private Sub GetOption(Frm As MSForms.Frame)
Dim i As Long
Dim Selected As String

With Frm
For i = 0 To .Controls.Count - 1
With .Controls(i)
If .Value = True Then
Selected = .Caption
Exit For
End If
End With
Next

Select Case .Caption
Case "Elective Choice #1": ElecChoice1 = Selected
Case "Elective Choice #2": ElecChoice2 = Selected
Case "Elective Choice #3": ElecChoice3 = Selected
End Select
End With

End Sub


Private Sub ClearOptions(Frm As MSForms.Frame)
With Frm
For i = 0 To .Controls.Count - 1
With .Controls(i)
.Value = False
.Enabled = True
End With
Next

Select Case .Caption
Case "Elective Choice #1": ElecChoice1 = ""
Case "Elective Choice #2": ElecChoice2 = ""
Case "Elective Choice #3": ElecChoice3 = ""
End Select

End With
End Sub

[Paul_Hossler]

@Paul


ec1.List = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
ec2.List = ec1.List
ec3.List = ec1.List

NB. The listindices are always -1 if you assign a new list to a combobox/listbox



Private Sub cmdAdd_Click()
sheets("dataEntry").Cells(Rows.Count, 1).End(xlUp).offset(1).resize(,5)=array(txtlName,txtfName,ec1,ec2,ec3)

for each it in controls
if typename(it)="TextBox" then it=""
if typename(it)="ComboBox" then it.listindex=-1
next
End Sub



1. Yes

2. Yes

3. Yes, but ...

Good input but I think #3 is a little obscure so I'd prefer to take the 'wordy' route

[netwerkz]

Hey SamT!

You have always been a great resource. I looked into your code and do not see where the electiveChoice1, electiveChoice2, or electiveChoice3 does not fill in on the "dataEntry" worksheet. I do like what you've done with the code and it does make sense to me, but I do see where the output is other than the MsgBox that pops up with the results.

I also need to build a class roster based on random draw from each electiveChoice (1,2, & 3). I'm guessing that will be a pivot table per column and a separate output module (possibly a new worksheet that is printed off). The remaining students who are not randomly selected to fill the class needs to be dumped into the next electiveChoice.

[netwerkz]

Hey Paul,

Thank you for your input. I tried your method and find it interesting. Do you know of a way to build a class roster based on the input per electiveChoice?

JD

[Paul_Hossler]

I made some changes to incorporate comments from others

I'm not sure what you mean by class roster, so I did what I think a class roster is

20265




Option Explicit

Public arrClasses As Variant

Sub AddData()
arrClasses = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")
frmRosterCreator.Show
End Sub

Sub BuildRoster()
Dim rChoices As Range
Dim a As Long, r As Long, c As Long
Dim wsRoster As Worksheet

arrClasses = Array("Baking", "Basket Weaving", "Being Handy", "Computer Lab", "Cooking", "Intro to Excel", "Library", "Reading", "Skateboarding", "Other")

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("ClassRoster").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add.Name = "ClassRoster"
Set wsRoster = Worksheets("ClassRoster")
wsRoster.Cells(1, 1).Resize(1, UBound(arrClasses) + 1).Value = arrClasses

Set rChoices = Worksheets("dataEntry").Cells(1, 1).CurrentRegion

With rChoices
For r = 2 To .Rows.Count
For c = 3 To 5
If Len(.Cells(r, c).Value) > 0 Then
a = Application.Match(.Cells(r, c).Value, wsRoster.Rows(1), 0)
wsRoster.Cells(wsRoster.Rows.Count, a).End(xlUp).Offset(1, 0).Value = .Cells(r, 1).Value & ", " & .Cells(r, 2).Value & " (" & (c - 2) & ")"
End If
Next c
Next r

End With

wsRoster.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit

End Sub

[SamT]

do not see where the electiveChoice1, electiveChoice2, or electiveChoice3 does not fill in on the "dataEntry" worksheet.I gota leave something for you to do :devil2:

Here is the Module1 code that creates and fills individual Class Roster sheet from the (new) Master Roster sheet. I will let the acknowledged master of arrays, snb, write the code to fill the Master Roster from the Data Entry sheet.

See attachment to see some new code techniques that may interest you. Note that I did NOT check to see if a sheet already existed before adding a new one, so be sure and delete the Sheet, "Baking Class Roster" the code made for me during testing, before you test it.

Option Explicit

Sub dataEntry_Click()
UserForm1.Show
End Sub

Sub CreateClassRosters()
Dim LR As Long
Dim cCel As Range
Dim rCel As Range
Dim StudentID As Range
Dim ShtNme As String


With MasterRoster 'I said, See attachment.

For Each cCel In Range(.Range("B1"), .Range("B1").End(xlToRight))
LR = 0
If cCel.Offset(1) = -1 Then GoTo cCelNext
LR = .Cells(Rows.Count, cCel.Column).End(xlUp).Row
If LR = 2 Then GoTo cCelNext
'OK, the class is not closed, and it has students assigned

ShtNme = cCel.Value & " Class Roster"
Worksheets.Add After:=Sheets(Sheets.Count)
DoEvents
Sheets(Sheets.Count).Name = ShtNme
RosterTemplate.Cells.Copy Sheets(ShtNme).Cells(1)
DoEvents



For Each rCel In Range(.Cells(3, cCel.Column), .Cells(LR, cCel.Column))
rCel.Value = CLng(rCel.Value)
If rCel = 0 Then GoTo rCelNext

Set StudentID = DataEntry.Columns(1).Find(rCel)
With Sheets(ShtNme).Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Value = StudentID.Offset(, 1).Value
.Offset(, 1).Value = StudentID.Offset(, 2).Value
End With

rCelNext:
Next rCel



cCelNext:
Next cCel
End With 'MasterRoster
End Sub


Note that I am writing all my code as if all the class names you gave are dummy values. I am also keeping in mind that it is possible write code to auto create the Frames and OptionButtons by merely editing the Class Names on the Master Roster sheet and the number of Option columns on the Data Entry sheet

[SamT]

@ snb, see my above :devil2:

[snb]

@netwerkz

Did you overlook #5 ?

[snb]

@Sam

I did.
I was shocked :crying: not to find a class module in this class oriented 'problem'.

[SamT]

@ snb,

I thought about using an Event Class, but decided that was a little advanced for this new guy. Figured to give him some Basic VBA first, then add new concepts as things progressed.

I was surprised that Paul didn't incorporate an Event Class in his example.

[Paul_Hossler]

I was surprised that Paul didn't incorporate an Event Class in his example.

K.I.S.S.

(= Keeping It Simple, Sam)

[netwerkz]

Hey Guys!

Thank you for all yalls help. Now, I have a simple issue I just cannot to figure out.



'
' Counts the number of students per class and ignores the blank rows within a specific rows
'
Dim rng As Range
Set rng = Worksheets("1st Choice Roster").Range("A7:A49")
rng1 = Excel.WorksheetFunction.CountA(rng)
Range("A50").Select
With Selection
rng1.PasteSpecial Paste:=xlPasteValues
End With


I know I might be in the right area, but I believe I'm going in the wrong direction. I put it directly into the module for a specific sheet. I have a total of (3) three worksheets that I now create (with yall's help!!!).

[SamT]

@ netwerkz,

It will help us if you specify whose code from which post# you are referring to. Of course that person will recognise their own code, but the rest of the contributors will have to search the entire thread to figure it out.

[netwerkz]

@ netwerkz,

It will help us if you specify whose code from which post# you are referring to. Of course that person will recognise their own code, but the rest of the contributors will have to search the entire thread to figure it out.


Hey SamT et al.,

I went with a combination of all (3) three suggestions. For those who tend to think of my ability to figure things out for myself (based on a few of the above comments), I figured out how to do what I was looking for. I come on this forum seeking help to remind me which direction I need to go in.

I thank you for your help.

Netwerkz

[SamT]

I went with a combination of all (3) three suggestions. I figured out how to do what I was looking for.
Best way of all.

Feel like sharing your final Code? All of us are interested in new ways and it might help some other seeker in their quest for answers.