Listbox to popup from search within a userform

tomsov

New Member
Joined
Mar 31, 2017
Messages
24
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!

Screenshot 2021-07-18 203549.jpg
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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:
VBA Code:
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):
VBA Code:
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

New Member
Joined
Mar 31, 2017
Messages
24
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
VBA Code:
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:
VBA Code:
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):
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
You're welcome and thanks for letting me know.
 

tomsov

New Member
Joined
Mar 31, 2017
Messages
24

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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.
VBA Code:
Sub Example()
    
    Dim uf As UserForm1
    Set uf = New UserForm1

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

tomsov

New Member
Joined
Mar 31, 2017
Messages
24
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:
VBA Code:
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.
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,147
Office Version
  1. 2013
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,424
Messages
5,724,260
Members
422,542
Latest member
jedidia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top