Userform Listbox problem

[redstang423]

Hey guys,

I am looking for suggestions on how to approach this problem I'm having. I have data that I need to allow people to edit using a user form. I don't want them editing the data directly since there is a good chance they would screw it up. The specific data is categories and category weight. Here is an example of what the data might look like and how it would ideally be displayed:

Category A | 25 %
Category B | 25 %
Category C | 30 %
Category D | 20 %

I need to be able to display both sets of information on the form, and it needs to be obvious how they relate. My first inclination was to create two listboxes and link them so that when you clicked a line on one, it would also highlight the same line in the other listbox. The category weight, however, may not be unique, so I can't just go to the value and I'm not aware of a property that relates to the position within the list box (i.e. 4th one down).

My next idea was to simply not worry about highlighting the appropriate weighting value in the second list box when the user clicks the category name (in the first list box) to edit. The issue with this method is that the list of categories could exceed the single screen (I'm constrained on the listbox size) and require scrolling vertically within the listbox for categories. The weighting would not scroll with it and it could easily confuse.

If I need to, I can go to one listbox and have a text box that populates the weighting when a category is clicked. I would highly prefer to have all the information visible at once though.

Does anyone have any suggestions on how I might do this?

[DonkeyOte]

I'm a bit confused...

Are you not able to use a ListBox control with Column Count of 2 to display the data together ?

That said it's not really clear what the planned workflow is - at least not to me.
What exactly is the end user altering etc...

I'm not aware of a property that relates to the position within the list box

Perhaps see: .ListIndex (0 based)

It might be worth posting a dumbed down version of what it is you're trying to do - ie create a UserForm and outline desired workflow etc... that will help others get to grips with your requirements I think,.

[redstang423]

The .ListIndex property was exactly what I was looking for but couldn't find.

The column count actually is ideal for what I'm trying to do - that was another property I've never used before and didn't realize existed. It seems to do exactly what I need, but it isn't obvious to me how to populate both columns with data when the form is initialized. I can only get data into the first column. The Microsoft VBA help wasn't useful in that end...

[redstang423]

Here is a very quick mock up of the screen the user would see. I need the 2 column listbox to populate the category name on the left (as it does now) and the category weight in the right column. I'm struggling to get the second column populated.

I've dropped my idea of using a separate listbox for each column since this is much easier.

Note that I added three buttons on the user form that do nothing. The end user would end up selecting a category from the list box and then editing the category name or weighting via a pop up depending on the button they chose. I don't have an issue writing the code for that, so I didn't bother to add anything in for this example.

Thanks for the help!

[redstang423]

I found the solution. Doing the standard "with xxxx" ".additem" commands will populate the first column. The way I found to populate the second column is with

Please Login or Register  to view this content.

Where i is the row number. Note that the first row is row 0, so when using this in conjunction with a Do loop, make sure i's intial value is 0.

Thanks DonkeyOte, for pointing me in the right direction!

[DonkeyOte]

If you have the values stored in a range (per your sample) then you don't need to iterate & add items to the control in the UF Initialize event.
Simply set RowSource property of the ListBox control to be that range (Sheet1!A2:B5) ... Name the range if preferred