I'm brand new to VBA userforms, and I've been struggeling with a problem for months. In a time-schedule program, I trying to add a new collegue to my program via userforms. The idea is to type the name of the new collegue and select the experience level (Master, Experienced etc.) at some specific work tasks (that changes over time). For that purpose I prefer using option buttons. I managed to build the userform on the fly as you can see below in a simplified version. You should be able to run the code as it is. My problem is to extract the values as selected. I've tried everything. It seems as if CodeModule.InsertLines totally destroys the dimensions of the variables in .GroupName, .Name, and .Tag, which makes it impossible to retrieve the values. Can anybody help? I would be very grateful.
Sub CreateUserForm2()
Dim myForm As Object
Dim WorkTasks() As Variant
Dim myValue As Variant
Dim a1 As Byte 'counter work tasks
Dim b1 As Byte 'counts number of work tasks to dispay on userform (=6)
Dim d1 As Byte
Dim e1 As Byte
Dim X As Integer
'add new worker's unique initials
myValue = InputBox("Type workers unique initials")
'Validation not shown here!
'Create all possible work tasks (Work tasks are dynamic - name and number varies over time. Below is simplified)
ReDim WorkTasks(0 To 1, 3 To 10)
For a1 = 3 To 10
WorkTasks(0, a1) = "Work_task_" & a1 'Name of work task
WorkTasks(1, a1) = "Yes" 'Should this work task be displayed [Yes/No]
If a1 = 5 Or a1 = 9 Then
WorkTasks(1, a1) = "No" 'Example to denonstrate work tasks that should not be displayed
End If
'MsgBox (WorkTasks(0, a1))
'MsgBox (WorkTasks(1, a1))
Next a1
'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
'Create the User Form
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)
'Counts the rows of work tasks to be displayed on the userform
For b1 = LBound(WorkTasks, 2) To UBound(WorkTasks, 2)
If WorkTasks(1, b1) = "No" Then
'do nothing 'skips the work task as it should not be displayed
End If
Next b1
'MsgBox ("b1: " & b1)
With myForm
.Properties("Caption") = "Choose worktask and level of experience for " & myValue & "."
.Properties("Width") = 500
.Properties("Height") = 15 + 35 + b1 * 15
End With
'Loop through the work tasks once again to populate the userform with 4 levels of radiobuttons for each work tasks
'NOTICE recycled variables: b1
e1 = 0
For b1 = LBound(WorkTasks, 2) To UBound(WorkTasks, 2)
d1 = 0 'Horizontal Counter
If WorkTasks(1, b1) = "Yes" Then 'Create 4 radiobuttons for this specific work task, as it should be displayed
e1 = e1 + 1 'Vertical Counter
'Create OptionButton 1st column
Set NewOptionButton = myForm.Designer.Controls.Add("Forms.optionbutton.1") '
With NewOptionButton
.GroupName = "Row_" & b1
.Name = "RowAndExperience_" & b1 & "_" & "1"
.Caption = "Master"
.Top = 15 + e1 * 15
.Left = 250 + d1 * 60
.Tag = "1_" & b1
End With
d1 = d1 + 1
'Create OptionButton 2nd column
Set NewOptionButton = myForm.Designer.Controls.Add("Forms.optionbutton.1")
With NewOptionButton
.GroupName = "Row_" & b1
.Name = "RowAndExperience_" & b1 & "_" & "2"
.Caption = "Experienced"
.Top = 15 + e1 * 15
.Left = 250 + d1 * 60
.Tag = "2_" & b1
End With
d1 = d1 + 1
'Create OptionButton 3nd column
Set NewOptionButton = myForm.Designer.Controls.Add("Forms.optionbutton.1")
With NewOptionButton
.GroupName = "Row_" & b1
.Name = "RowAndExperience_" & b1 & "_" & "3"
.Caption = "Inexperienced"
.Top = 15 + e1 * 15
.Left = 250 + d1 * 60
.Tag = "3_" & b1
End With
d1 = d1 + 1
'Create OptionButton 4nd column
Set NewOptionButton = myForm.Designer.Controls.Add("Forms.optionbutton.1")
With NewOptionButton
.GroupName = "Row_" & b1
.Name = "RowAndExperience_" & b1 & "_" & "4"
.Caption = "N/A"
.Top = 15 + e1 * 15
.Left = 250 + d1 * 60
.Tag = "4_" & b1
End With
'Create Optionbutton label
Set OptionButtonLabel = myForm.Designer.Controls.Add("Forms.label.1")
With OptionButtonLabel
.Top = 20 + e1 * 15
.Left = NewOptionButton.Left - OptionButtonLabel.Width - 325 '
.Width = 150
.Caption = "Select experience level: " & WorkTasks(0, b1)
End With
Else
'b1 = b1 + 1 'skips the work task as it should not be displayed
If b1 >= UBound(WorkTasks, 2) Then '
Exit For 'No more work tasks to be displayed
End If
End If
Next b1
'Create CommandButton OK
Set NewButton = myForm.Designer.Controls.Add("Forms.commandbutton.1")
With NewButton
.Name = "cmd_1"
.Caption = "Save & exit"
.Accelerator = "M"
.Top = b1 * 15
.Left = 300
.Width = 66
.Height = 20
.Font.Size = 8
.Font.Name = "Tahoma"
.BackStyle = fmBackStyleOpaque
End With
'Create CommandButton Cancel
Set NewButtonCancel = myForm.Designer.Controls.Add("Forms.commandbutton.1")
With NewButtonCancel
.Name = "cmd_2"
.Caption = "Cancel"
.Accelerator = "M"
.Top = b1 * 15
.Left = 370
.Width = 66
.Height = 20
.Font.Size = 8
.Font.Name = "Tahoma"
.BackStyle = fmBackStyleOpaque
End With
'Add event-hander subs for the CommandButtons
With myForm.CodeModule
X = .CountOfLines
.InsertLines X + 1, "Sub Cmd_1_Click()"
.InsertLines X + 2, " Dim ctl As Object"
.InsertLines X + 3, " For Each ctl In Me.Controls"
.InsertLines X + 4, " If ctl.Tag <> """" Then"
.InsertLines X + 5, " If ctl.Value = True Then"
.InsertLines X + 6, " msgbox (ctl.Tag)"
.InsertLines X + 7, " End If"
.InsertLines X + 8, " End If"
.InsertLines X + 9, " Next ctl"
.InsertLines X + 10, " "
.InsertLines X + 11, " Unload Me"
.InsertLines X + 12, "End Sub"
'*******************************************************************************************
'In the code above: How do I extract the selection of the radiobuttons from the Userform???*
'*******************************************************************************************
End With
VBA.UserForms.Add(myForm.Name).Show
'Delete the form (Optional)
ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub
