PDA

View Full Version : Solved: How to Access Objects/Tools (ie ListBoxes) of a UserForm?



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

mvidas
06-21-2006, 11:08 AM
Hi nitt,
Those items are referred to as controls in vba, so you would use:'For Each CompItem In VBComp.Collection
For Each CompItem In VBComp.ControlsMatt

nitt1995
06-21-2006, 12:32 PM
Thanks!

mvidas
06-21-2006, 01:16 PM
Glad to help! Gotta love the VBA syntax :)