I am working with a function to return the value selected by an option button from
https://www.engram9.info/excel-2003-vba/a-useful-but-not-so-simple-dynamic-userform-example.html.
However, it gives an error "Type Mismatch" not too far in. I have stripped the code down a bit to simplify the question, hopefully leaving everything necessary to answer the question.
I have not been able to find the information needed.
Function GetOption(OpArray, Default, Title)
'https://www.engram9.info/excel-2003-vba/a-useful-but-not-so-
'simple-dynamic-userform-example.html
Dim TempForm As Object
Dim NewOptionButton As Msforms.OptionButton
Dim MaxWidth As Long
'Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
TempForm.Properties("Width") = 800
'Add the OptionButtons TopPos = 4
MaxWidth = 0
Set NewOptionButton = TempForm.Designer.Controls m
'Type mis-match here
End Function
Here is the calling sub:
Sub TestGetOption()
Dim Ops(1 To 5)
Dim UserOption
Ops(1) = "North"
Ops(2) = "South"
Ops(3) = "West"
Ops(4) = "East"
Ops(5) = "All Regions"
UserOption = GetOption(Ops, 5, "Select a region")
Debug.Print UserOption
MsgBox Ops(UserOption)
End Sub
Error message is "Type mis-match"
When adding a new option button, you'll need to use the Add method of the Controls object, and you'll need to provide the ProgID for the control...
Set NewOptionButton = TempForm.Designer.Controls.Add("Forms.OptionButton.1")
For additional information, have a look at the following link...