Sort a listbox

[FRIEL]

Hello all

Should be an easy one here

I have 2 List boxes in a user form
and 2 butons

on run, a range of cell values car taken into Listbox1

the 2 buttons run like this.

Please Login or Register  to view this content.

you select an item in the list box and then push the button and the item moves into the other list box

i want the boxes to be sorted in to alaphabetical order when i move an item over.

[Richard Buttrey]

Hi,

How are you populating the list boxes?
If you're using the .RowSource property then sort that range as part of the update process.
If you're not using .RowSource then consider doing so.

Regards

[FRIEL]

apologies for the late reply

this is the code i am using to populate the listboxes

Please Login or Register  to view this content.

what is the .RowSource property?

[Richard Buttrey]

Hi,

I prefer populating List and Combo boxes with this rather than looping through cells as you are doing. It's quicker for a start.

Click on the ListBox and then in the Properties window scroll down until you see 'RowSource'

This takes a reference to a range of cells, either in the format "A10:A20", but I prefer naming the range in the workbook and then just entering the range name in the RowSource property. If you already have a range called 'Email' then just enter that name. Then you don't need any code in the Form initialise event.

Regards

[FRIEL]

ok i will try that
can i set it to use a list that is on another sheet?
and how do i set it using code?

i got it to work for populating the boxes
but now the 2 buttons i described before do not work when i use this method

i get the error

Run-time error'-2147467259 (80004005)';
Unspecified error

also

how does this help me sort the listbox contents??

[Richard Buttrey]

Hi,

As I said if you give a dynamic name to the range containing the list, just enter that name in the Row Source property of the box.

And as I also said you don't therefore need any code.

I don't know why your buttons don't work - you'll need to upload your workbook.

All your code needs to do is manage the entries in the two lists, adding or deleting as necessary then sorting. The dynamic range name and the Row Source property will automatically take care of the box contents.

Regards

[snb]

Please Login or Register  to view this content.

do not use additem to populate a listbox/combobox but use the method 'list'.

Please Login or Register  to view this content.

[Richard Buttrey]

Or indeed the RowSource property which behind the scenes is exactly what .List is doing.

Rgds

[snb]

rowsource can cause unpleasant errors when using VBA.

[FRIEL]

Hi Guys again sorry about the delay, im at work and keep getting pulled away

i have attached a sample book
the idea is that you pick names that you will send an email to.
I am still struggling with this

if i use the RowSource properity then my buttons do not work

if i use snb's solution
it kind of works

it will run and then the form will dissapear

Please Login or Register  to view this content.

what is that pwrt of the code actualy doing?

[snb]

Too much to explain; just have a look in the attachment

[FRIEL]

i have a problem here
my work firewall wont let me DL

could i PM you my my email address to send it?

[snb]

Your employer must be MI5 ?

[FRIEL]

lols no they are just strict.
was surprised i was alowed an upload.
recieved that file but there was no content in there
just the list of names.
nothing in the VBA either

[snb]

Ask your IT staff for spectacles !
All VBA is in the userform.

[FRIEL]

no user form in there.

[snb]

There is, but probably those IT-guys removed it.

See whether you are able to download this zipped file

[FRIEL]

no userform in there either
i dont know what happened there
any chance you could resend?

[FRIEL]

i can download it
that was quite clever of you
but still no userform there
how frustrating

[snb]

Please post the file you downloaded.

[jaslake]

@snb
Hi old friend...I still follow your work...neat stuff. I to downloaded both files you posted and there is no code or UserForm in either file. I had this issue several weeks ago. I made a copy of the file and uploaded the copy...it eventually worked...don't know why.

[snb]

@John

Thanks for your feedback.
I downloaded the zipfile form this thread: no problem.

I will post a new workbook, that will open the userform automatically.

[jaslake]

@snb

Interesting...the code is there and the UserForm is there. In Excel 2000, the UserForm opens when the file is opened. In Excel 2007, the UserForm does not open when the file is opened. One CAN open the UserForm in Excel 2007 manually.

@ FRIEL

Modify this code

Please Login or Register  to view this content.

[snb]

@John

I can hardly believe that the other files I posted in this thread didn't contain the userform.(If I download them they do).
Maybe if you download them, rename into xlsm, open in 2007.

[jaslake]

@snb

As I mentioned, I had the same issue several weeks ago. I also downloaded the file that I had posted. The first time, it contained the code and the UserForm. I uploaded it again. Opened it on the Forum and the code and UserForm were not there.

No idea what caused this.

I haven't yet but I'll do as you suggested

rename into xlsm, open in 2007.

and let you know.

[FRIEL]

ok this is very strange
the file you emailed me had no userform in either

i cant DL that last file again because of my MI5 style firewall

so the idea is that only SNB can see the contents of the userform?

i have uploaded the file i managed to DL and a zip file with the original and another with a userform in it can everyone see the any userforms?

[snb]

Yes the __snb suggestion_xls.xls you posted contains the userform. Are you familiar with the VBEditor ? (At-F11). 3 modules in there: Thisworkbook, Sheet1 and Userform 1.
Please use the version in post #22.

[FRIEL]

could you please zip the one in post 22
as i can DL zip files

yes i am familier with VBEditor

i have uploaded a screen shot of what i see

[snb]

Your screenshot: nope

I added the zipfile to post #22

[FRIEL]

wow man thats Great!

thanks allot
ill have a look and let you know

looks like it was worth all this hassle with the DLs
=D

[FRIEL]

im sorry to be such a pain but would it be possible for you to explain a few bits to me.
As you sig sugests i try to understand all the code i use, so that i am learning rather than just getting forums to write all of my macros.

how have you populated the first list box?

Please Login or Register  to view this content.

it works but i cant make any sense of that.


what is this part doing?

Please Login or Register  to view this content.

the list boxes are sorting but i cant see how

i can see how you are using the TAG property
thought that was clever
what does that normaly reference to?

thanks in advance

[snb]

- diminish the size of the vbeditor, so that you can see the macrocode and the Excel worksheet at the same time.
- let the macro play step by step: F8
- now you can see what happens in the worksheet during the execution of the macro.

If you want to know what "Tag" does: put a hyphen ' before the "tag-lines" and look what difference it makes.

The population of lst_addresses:

- the range containing names and emailadresses will be given a name : the construction of a 'named range'.
- in 1 line the code concatenates the names in column A and the emailadresses in column B
- puts the concatenation into a 1-dimensional array
- which populates the listbox using it's list property

( a worksheet is a 2-dimensional array (65536 * 256); a range is an 2-dimensional array (rows *columns); a listbox contains a 1 or 2-dimensional array; a combobox contains a 1 or 2-dimensional array)
You can get :

Please Login or Register  to view this content.

So it is rather easy to switch between listboxes/comboxes on the one hand and ranges in sheets on the other.

[FRIEL]

would i be right in the notes below?

Please Login or Register  to view this content.

what does resize do?

Please Login or Register  to view this content.

is this line naming the range then?
or is it already named manualy?


is this the line that concatenates and changes the range into a 1D array?

Please Login or Register  to view this content.

after the "=" i cant understand anything.

( a worksheet is a 2-dimensional array (65536 * 256); a range is an 2-dimensional array (rows *columns); a listbox contains a 1 or 2-dimensional array; a combobox contains a 1 or 2-dimensional array)
You can get :
Code:
x=ubound(sheets(1).cells.value)
y=ubound(sheets(1).cells.value,2)
or
x= UBound(Sheets(1).Range("A1:G40").Value)
y= UBound(Sheets(1).Range("A1:G40").Value,2)
or
x=Ubound(Listbox1.list)
y=Ubound(Listbox1.list,2)
or
x=Ubound(Combobox1.list)
y=Ubound(Combobox1.list,2)So it is rather easy to switch between listboxes/comboxes on the one hand and ranges in sheets on the other.

this part was even more confusing to me.

snb i realy appreciate you walking me through this.

[FRIEL]

I have marked this as solved
as it is pretty much but would
still like a bit of explanation on the above
it works but i donk quite know how

this makes it difficult for me to use the code in
other places and modify it to other uses