0

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

9
  • 3
    Not all elements in FormName.Controls are msforms.ListBoxes. Commented Dec 24, 2020 at 15:11
  • 1
    Change the type to Dim O As Control Commented Dec 24, 2020 at 15:14
  • Oh that is true, I did't know that would cause problems. Is there any way to loop only through ListBoxes in specific UserForm then? Commented Dec 24, 2020 at 15:15
  • 2
    @Warcupine stackoverflow.com/a/6060219/11683 Commented Dec 24, 2020 at 15:24
  • 1
    @gserg Ahh, that is a much better way. Honestly that makes a pretty good dupe target for this question. Commented Dec 24, 2020 at 15:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.