I'm creating small project in Excel, and because I'm a VBA newbie I do encounter a lot of problems that I'm trying to resolve on my own. However i can't cope with this:
I created Sub that accepts two objects: FormName and ControlName.
What i want it to do, is to loop through every Control in specific UserForm and populate every ListBox it encounters, from another ListBox.
I created this funny string comparison, because I need to operate on objects in order to execute the line with AddItem. This comparison actually works well, no matter how ridiculous it is. However when I launch the program, I got
Type Mismatch error
and to my surprise "Next" is being highlighted. I have no idea how to fix this, nor what is wrong.
Public Sub deploy(ByRef FormName As Object, ByRef ControlName As Object)
Dim i As Integer
Dim O As msforms.ListBox
i = 0
For Each O In FormName.Controls
If Left(FormName.Name & O.Name, 16) = Left(FormName.Name & ControlName.Name, 16) Then
O.AddItem (FormName.Podgląd.List(i))
i = i + 1
End If
Next
End Sub
I call this sub using:
Call deploy(UserForm1, UserForm1.ListBox3)
Above, I use Listbox3 because otherwise i got error saying that variable is not defined. However in my comparison I kinda override this.
If someone can explain in simple words, how to fix this type mismatch issue or how to write it in more elegant way
FormName.Controlsaremsforms.ListBoxes.Dim O As Control