Creating a ListBox

[netwerkz]

Good Morning!

I have a workbook with only (2) two worksheets, "Dashboard" & "FieldData", which includes a table of data (multiple rows & multiple columns) located on a separate worksheet. I need to use those values in the 2nd column (mostly numerical & some alpha-numeric) to populate a ListBox (on the "Dashboard" worksheet). When a user clicks on any item in the ListBox, a macro will be ran to filter the "FieldData" worksheet to identify only those rows with that specific value in column 2. Another macro will be ran to select all data displayed, copy it, and starting with cell "E11" paste that data on the "Dasboard".

I would like the user to be able to click another ListBox item to retrieve that items data in the same fashion, first by clearing the reported data and pasting the new values in the reporting area (again, starts in cell "E11"). It would be <i>REALLY</i> nice if this could be ran as a separate application.

This is some of what I believe I need. If you can simplify the code into a loop function, please let me know! It sure would save a long of typing time!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any help would be EXTREMELY appreciated!!!

JD

[netwerkz]

I should have included some of the data located on the "FieldData" worksheet...

Please Login or Register  to view this content.

[netwerkz]

Upon running my code, I get a"Object variable not set (Error 91)" message. I made a couple of changes thinking I might be able to get this to work since no one has posted a response...

Please Login or Register  to view this content.

I cleaned up the code since there were a lot of repetitious code by using the Call functions...

Please Login or Register  to view this content.

Here are the Call functions code...

Please Login or Register  to view this content.

[Norie]

Why are you creating the listbox at runtime?

Can't you just add it to the userform at design time and put the code to (initially populate) it in the userform's Initialize event?

PS Listboxes don't have an Initialize event and using one of a listbox's own events isn't really a good idea.

[netwerkz]

Why are you creating the listbox at runtime?

Can't you just add it to the userform at runtime and put the code to (initially populate) it in the userform's Initialize event?

PS Listboxes don't have an Initialize event and using one of a listbox's own events isn't really a good idea.

Norie,
I am not sure I understand your questions. Honestly, I would like to use a UserForm with a ListBox that is populated with the individual values in Column B. Each of the ListBox items would be clickable to run the filter identifying all records with that ListBox item that was selected (i.e. user clicks on ListBox item 2, the filter will hide all other records leaving only those records to be used). The script will copy the remaining data (after the filter) and paste it on the "Dashboard" for client use.


I'm getting lost in how to make this script work the way I see it in my head. I've been researching all over for information that will help me really understand what I need to do (by showing me) to get me what I want to see and not by giving me ambiguous information. I admit, I am still a newbie in VBA scripting.

[Norie]

There was a typo in my post.

Anyway, why not just add a listbox to your userform and put code to populate it in the userform's Initialize event?

For example, this should populate ListBox1 with the data from column B from the sheet 'FieldData'.

Please Login or Register  to view this content.

As for filtering, if you want to filter the data on column B using the value selected in the listbox you should only need one sub.

You could even use the Click event of the listbox, something like this.

Please Login or Register  to view this content.

That should get you started and once this part is up and running we can look into the clearing/copying etc.

PS Does column B contain numerical data?

[netwerkz]

WOW! Now that's some AWESOME explanations! Thank you!

Yes, column B (Field # - see the table below) does contain numeric data, but it also contains alpha-numeric data.

Please Login or Register  to view this content.

[netwerkz]

Ok, I added your code to this...

Please Login or Register  to view this content.

So, are you saying I would need

Private Sub ListBox1_Click()
If ListBox1.ListIndex <> -1 Then
ActiveSheet.Range("$A$1:$J$137").AutoFilter Field:=2, Criteria1:=ListBox1.Value
End If

...and to just remove the If ElseIf in my original code? If so, that would be cool!

[Norie]

The only thing that changes in your subs fieldSelect1, fieldSelect2 etc appears to be the criteria value and that appears to be coming from listbox, so you should only need one sub.

[netwerkz]

The only thing that changes in your subs fieldSelect1, fieldSelect2 etc appears to be the criteria value and that appears to be coming from listbox, so you should only need one sub.

Ok,

So, I interpret your last response as, the only subs needed to do what I am hoping to finish by 5pm MST is...

Please Login or Register  to view this content.

Well, I did that, but when I run it, I get "Error: 424 Object required" and my ListBox is not populated with anything.

[Norie]

There's a typo in the code but I don't know if it's the cause of the error.

Anyway, here's the corrected code.

Please Login or Register  to view this content.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[netwerkz]

The file "field guys data.xlsx" has been uploaded.

[Norie]

Sorry but not workbook has been uploaded.

Did you make sure to click Upload after you'd selected the workbook from your system?

[netwerkz]

Ok, I uploaded the file but the Attachments Paperclip doesn't allow that action. WEIRD!


Oh! there it is!

[Norie]

The code goes in the userform module, see the updated file I've attached.

[netwerkz]

Thank you, but that still does not populate the fields as desired. It populates the ListBox, but clicking the ListBox item (like 1) does not filter FieldData to Field #: 1 and display the data in the boxes. I actually wouldn't mind if the data is displayed in one BIG box, like a text box.

[Norie]

I thought you wanted to have the data on the dashboard?

If that's not what you want I would suggest you get rid of all the textboxes on the userform and replace them with a large listbox.

We can then write code to populate this second listbox based on what's selected in the first.

[bakerman2]

Select an item in listbox1. List appears in listbox2.

[netwerkz]

Update

I’m having problems with a ListBox ActiveX I placed on my 1st worksheet (called “Dashboard”). I have a 2nd page (called “FieldData”). I think I’ve populated it correctly, but I cannot get a single click on any ListBox item to run a Sub (macro) named after the ListBox item.

I’m past my deadline for submitting it to my boss and I need help!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It’s supposed to be pretty simple, click on the Field# in the ListBox. That Sub will filter the FieldData to that criteria,
copy the data, and paste it in a specified location on the “Dashboard” page. I want the user to be able to repeat for any Field#
one right after the other. I have built-in clearing functions (found above) to clear the specified location.

The Field_# macros work just fine individually. The problem is with the "If ListBox1.Text =". I keep getting the "Variable not defined" error. What should I define it as?

Can you help?
JDS

[bakerman2]

Try this one.

[netwerkz]

Try this one.

THAT's How it's DONE!!!

Thank you, bakerman2!

[bakerman2]

You're very welcome but since you have a lot of different Fields in Column 2 to filter on your Data sheet.
Have you considered on how to load them all in your Listbox ?

[netwerkz]

You're very welcome but since you have a lot of different Fields in Column 2 to filter on your Data sheet.
Have you considered on how to load them all in your Listbox ?

I have and I've seen posts that explain how to reduce the repeated Field_#'s. I've seen one where ListBox1 is populated from a 3rd worksheet. The list is renamed in the upper-left portion of the Formula Bar and when entering into "DesignMode", right-clicking the ListBox1, and selecting "Properties". In the "Properties" window, change "ListFillRange" to "FieldNumber" (without quotes) and hit enter. Get out of "DesignMode" by clicking it in the Ribbon and go back to the spreadsheet and there it is! ListBox1 automatically updates with the values on the other sheet.

I've also seen where devs are using a complex formula to reduce the number of repeats. I tend to steer away from that idea, because I would be concerned that by the formulated reduction of the list items would reduce the number of records that are summed under the display window.

[bakerman2]

Because I'm in a good mood today.

[netwerkz]

Because I'm in a good mood today.

Just wanted to say thank you once again bakerman2!