ListBox help

[johnny_p]

Hi all

Could someone please help or provide some direction for me please as I’m a little stumped?

I'm looking to add a listbox (or similar) to my userform which shows inputs held within Columns in a sheet.

The listbox needs to show 5 columns and then the individual columns need to show ranges held in sheet ranges for example Column 1 - "B15:B64", Column 2 - "C15:C64", etc

It’s not entirely clear how to go about this within the properties section

Thanks for looking

[stnkynts]

This will populate your listbox as requested (assuming listbox is named Listbox1). Note the placement of the code in the UserForm_Initialize event

Please Login or Register  to view this content.

[Norie]

Here's another method.

Please Login or Register  to view this content.

[johnny_p]

Thanks for the qwick response.

It works but it puts all the info in the first column of the Listbox, I was looking for something that would have 5 columns from left to right.

This will populate your listbox as requested (assuming listbox is named Listbox1). Note the placement of the code in the UserForm_Initialize event

Please Login or Register  to view this content.

[stnkynts]

Set for non-contiguous rows

Please Login or Register  to view this content.

[johnny_p]

Your a star!

Thank you very much for your help.

I'm just trying to format the range, however it does not like it.

Any suggestions?

Thanks again

Please Login or Register  to view this content.

Set for non-contiguous rows

Please Login or Register  to view this content.

[stnkynts]

Do you mean like this?

Please Login or Register  to view this content.

[johnny_p]

Thanks

It just debugs on this now

Please Login or Register  to view this content.

Do you mean like this?

Please Login or Register  to view this content.

[stnkynts]

Submit the code as a whole for what you are trying to do. Submitting a copy of your worksheet with any sensitive information removed will be helpful.

[johnny_p]

Thanks for sticking with me on this.

Unfortunately the userform and the code is a bit of a monster with loads of sensitive info. If I took it all out it would crash at every opportunity

The range of cells that I am looking to format are numbers with two decimal points

[Norie]

Do you want to display both columns formatted as they are on the worksheet?

Please Login or Register  to view this content.

[stnkynts]

Hopefully Norie's solution works for you because I am at an impass. I am not able to understand the scenario that you are dealing with and therefore am not able to answer your question.

[johnny_p]

I greatly appreciate your help so far on this. Its works 99%. Its just the formatting issue on the last two columns.

Would it have anything to do on how the array is set up being a "xlCellTypeConstants" I have try the "xlCellTypeAllFormatConditions" but it liked this less.

Hopefully Norie's solution works for you because I am at an impass. I am not able to understand the scenario that you are dealing with and therefore am not able to answer your question.

[johnny_p]

Thanks for this Norie

This does allow me to change the format who I want, however how would I set this up to work in the same way as stnkynts is designed?

I would need to have four columns within the list box with different ranges

Thanks again

Do you want to display both columns formatted as they are on the worksheet?

Please Login or Register  to view this content.

[romperstomper]

Did you try just changing this line in stnkynts code:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[johnny_p]

romperstomper your a legend!

Thank for coming to my rescue. I cant belive I didn't try that! Thats why i'm a novice!

Did you try just changing this line in stnkynts code:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.