nitt1995
06-21-2006, 09:27 AM
I want to be able to put various ToolBox Controls (ie TextBoxes and ListBoxes) of a UserForm into a collection, but I am not sure how to access/call specific controls.
For example, if I have three textboxes named Box1, Box2, and Box3, how can I put these into a collection so I can selectively turn off Box3 if it is not required, without creating explicit select/case logic.
Below is an example sub that I thought would work, VBComp is filtered to only examine UserForms, but I don't know how to go deeper to find the Controls on the userform. I thought VBComp.Collection would work, but it did not (for me)
Sub ListAllFormComponents()
Dim VBComp As VBComponent
Dim Msg As String
Dim CompItem As Object
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If (VBComp.Type = vbext_ct_MSForm) Then
Msg = Msg & VBComp.name & vbCrLf
For Each CompItem In VBComp.Collection
Msg = Msg & vbTab & CompItem.name & vbCrLf
Next CompItem
End If
Next VBComp
MsgBox Prompt:=Msg
End Sub
For example, if I have three textboxes named Box1, Box2, and Box3, how can I put these into a collection so I can selectively turn off Box3 if it is not required, without creating explicit select/case logic.
Below is an example sub that I thought would work, VBComp is filtered to only examine UserForms, but I don't know how to go deeper to find the Controls on the userform. I thought VBComp.Collection would work, but it did not (for me)
Sub ListAllFormComponents()
Dim VBComp As VBComponent
Dim Msg As String
Dim CompItem As Object
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If (VBComp.Type = vbext_ct_MSForm) Then
Msg = Msg & VBComp.name & vbCrLf
For Each CompItem In VBComp.Collection
Msg = Msg & vbTab & CompItem.name & vbCrLf
Next CompItem
End If
Next VBComp
MsgBox Prompt:=Msg
End Sub