Listbox to popup from search within a userform

[tomsov]

This one is a bit complicated to explain so I'll do my best:
I have an index (main) userform that displays open orders using a set of listboxs. When I enter either a part number or description into a textbox, listbox1 displays a set of results that are exact matches and wildcard matches - any result can be selected and it will populate listbox2 showing a list of customers that have open orders of that specific part. So at the moment a description can be entered such as "Mk2 Bonnet", listbox1 shows every entry from a database containing "Mk2","Bonnet" etc - the user clicks on the appropriate part and this populates listbox2. Listbox2 shows all the customers that have the Mk2 Bonnet on order.
So, all this works great - but it takes up a load of screen space on some of the terminals that have small screens.

What I'd like to do is to have Listbox1 popup over the top of everything to show the relevant data when the user enters the part number or description into the textbox.

My first thought was to use inputbox and populate it with the result from the search routine, but then thought about creating another 'popup' userform that is called from the index userform when the search is performed.
So far I can make the second userform popup, but am struggling to set the focus on it from the code within the index userform (the main controlling userform) so that the listbox on the popup userform is always blank. I think it may be an issue with passing data from one form to another?

The error produced is "Error 424, Object Required" which seems like it makes sense, just I can't seem to find out how to pass the results from userform1 to userform2 and run code within userform1 that shows results on userform2.

Hope all this make sense! Below is a screenshot of what I'm trying to achieve (but with a populated userform2). Thanks!

 

[GWteB]

Hi @tomsov, a Userform is a Class which can have certain advantages over the straightforward coding of program flow. These advantages are not always utilized. A userform is a so-called predeclared Class, and its default instance can therefore be called directly using the .Show method, which many people often do.
Although it is not necessary, you can also instantiate a userform, as in

Dim SomeForm as UserForm1
Set SomeForm = New UserForm1
SomeForm.Show

In this way, multiple instances of the same userform can be used simultaneously. Because a userform is a Class, you can add methods and properties yourself. That way, data that the userform depends on can easily be transferred to the Userform. Of course, this also applies in reverse.
So you can create a pop-up list box that behaves like a regular list box. Whether or not the list box contains a population can therefore also be influenced from another place, by means of custom properties and methods of your own tailor made Class. For example, you could add an Add method to your userform, like in:

Public Sub Add(ByVal argItem As Variant)
    Me.ListBox1.AddItem argItem
End Sub

Before displaying the userform, you're able to populate the list box on it, like in (minimalist example):

Sub Example()
    
    Dim uf As UserForm1
    Set uf = New UserForm1

    uf.Add "some list box item"
    uf.Show
End Sub

So the trick is to think abstractly, and in this case to wrap the most commonly used native methods and properties of the list box in custom made methods and properties of the host userform.
Hopefully this gives you an idea of a possible approach.

 

[tomsov]

Hi @tomsov, a Userform is a Class which can have certain advantages over the straightforward coding of program flow. These advantages are not always utilized. A userform is a so-called predeclared Class, and its default instance can therefore be called directly using the .Show method, which many people often do.
Although it is not necessary, you can also instantiate a userform, as in

Dim SomeForm as UserForm1
Set SomeForm = New UserForm1
SomeForm.Show

In this way, multiple instances of the same userform can be used simultaneously. Because a userform is a Class, you can add methods and properties yourself. That way, data that the userform depends on can easily be transferred to the Userform. Of course, this also applies in reverse.
So you can create a pop-up list box that behaves like a regular list box. Whether or not the list box contains a population can therefore also be influenced from another place, by means of custom properties and methods of your own tailor made Class. For example, you could add an Add method to your userform, like in:

Public Sub Add(ByVal argItem As Variant)
    Me.ListBox1.AddItem argItem
End Sub

Before displaying the userform, you're able to populate the list box on it, like in (minimalist example):

Sub Example()
   
    Dim uf As UserForm1
    Set uf = New UserForm1

    uf.Add "some list box item"
    uf.Show
End Sub

So the trick is to think abstractly, and in this case to wrap the most commonly used native methods and properties of the list box in custom made methods and properties of the host userform.
Hopefully this gives you an idea of a possible approach.

GWteB, that is a fantastic answer - really informative and very useful indeed. Thank you very much, I'm going to put this to use this evening. Greatly appreciated!

 

[GWteB]

You're welcome and thanks for letting me know.

 

[tomsov]

You're welcome and thanks for letting me know.

I've played around a bit with the codes but I'm getting this error: 'Compile Error: Method or Data Member Not Found', it's the uf.Add "stuff" line that's being hi-lighted. I've added the 'Public Sub Add(ByVal argItem As Variant)' to the popup userform (thinking the me.listbox is ref the one that the code is in) and the 'Sub Example()' bit of code to the Index Userform (the main control user form) [all references altered to the listbox names/variable refs etc that I'm using in the forms]. It seems as though excel isn't seeing the 'Add' keyword as a executable reference, I've tried altering the word to something different in both the popup and index refs to it with the same resulting error. Hmm, strange... Will play around some more with it and see what happens.

 

[GWteB]

I would recommend that you try the sample code in a new (empty) workbook. There is then no unintended influence of other code, such as an event handler for example.
Add a UserForm1, put a ListBox1 on it and add a Module1.
Paste this code into the Userform1 module:

Public Sub Add(ByVal argItem As Variant)
    Me.ListBox1.AddItem argItem
End Sub

Paste this code into Module1, give it a run or better yet, step through the code with F8, and see what happens.

Sub Example()
    
    Dim uf As UserForm1
    Set uf = New UserForm1

    uf.Add "some list box item"
    uf.Show
End Sub

 

[tomsov]

I would recommend that you try the sample code in a new (empty) workbook. There is then no unintended influence of other code, such as an event handler for example.
Add a UserForm1, put a ListBox1 on it and add a Module1.
Paste this code into the Userform1 module:

Public Sub Add(ByVal argItem As Variant)
    Me.ListBox1.AddItem argItem
End Sub

Paste this code into Module1, give it a run or better yet, step through the code with F8, and see what happens.

Sub Example()
   
    Dim uf As UserForm1
    Set uf = New UserForm1

    uf.Add "some list box item"
    uf.Show
End Sub

Works absolutely great. I know that I must be implementing it incorrectly into my existing code, so now I know the fundamentals of how this works I'll set to and try and get it working on my userforms. Thanks again, you've been a fantastic help!

 

[GWteB]

Thanks for letting me know. Sometimes the trick is not to look at a problem from the perspective of VBA, but more abstractly. Once you understand how you can bend things to your will, you will eventually discover more and more possibilities. Good luck!

EDIT:
this thread is about a similar topic