Help With Alphabetical Order In Listbox

[VBTroubles]

Hello, i have a user form which i use to sort out my pattern numbers however as i have been adding to my spreadsheet and there gets more and more i need to find a way to sort them alphabetically.

Snag_1b9d3fe7.png

This is My Spreadsheet

Snag_1ba000cc.png

This is My userform

Listbox is called ListboxA
A-Z Button is called AAZ, to click to sort if alphabetically, or for it to automatically do it
Userform is called PatternsA

when its sorted alphabetically i would like the numbers to go with them if thats possible?

[nigelog]

Private Sub UserForm_Initialize()
Dim lrow As Long
Dim rng As Range
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("B1:B" & lrow), _
   order1:=xlAscending, Header:=xlNo
ListBoxA.List = rng.Value
End Sub

and to put back in order on worksheet

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim lrow As Long
Dim rng As Range
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("a1:a" & lrow), _
   order1:=xlAscending, Header:=xlNo
End Sub

[VBTroubles]

thank you for such a fast responce and it works what do i need to change to do the same with b, i changed it to ListBoxB, but its not working?

[dominicb]

Good morning VBTroubles

This link demonstrates how to sort the items within the listbox using a bubblesort technique (look about halfway down the page), this will not alter the sequence of your source list.

HTH

DominicB

[nigelog]

what range does listbox b refer to??

[VBTroubles]

Attachment 640308

Attachment 640309

Attachment 640310

Dosnt seem to be working =[

[dominicb]

Hi VBTroubles

It would be easier for all concerned if you were to post a workbook with what you have so far. Anyone looking at this post will have to recreate what you already have to help out.

HTH

DominicB



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[VBTroubles]

ill just send the file,

Snag_1bd74df7.png

not too sure where to go from here?

I just need it to sort everything but cant get it to work properly

[nigelog]

That workbook is annoyingly set to show the menus only and when you can see the worksheet the menu is modal so can't get further. I see that the menu loads the listbox by a letter variable. Probably the same listbox. I'll leave as not in a position to turn off macros etc to look at it

[VBTroubles]

if you click on add pattern then you can close the form to get to the spreadsheet, sorry about that i removed the ability to close it as my uncle kept clicking it leaving the workbook open in the background

[nigelog]

when i get to add pattern I see no spreadsheet here. Same with see spreadsheet option - I can see in background but userform close turned off

[VBTroubles]

click open spreadsheet, then add pattern then the x to close, sorry its a pain i know

[nigelog]

Sorted.
You have a userform for each letter which is unnecessary.
You can select the letter from the choice menu and pass the letter as a variable via a class module on the buttons to one userform with the listbox populated by a filtered and sorted list.

As a project I would love to complete but travelling this weekend.

The outline is above if anyone would care to assist with what VBTroubles has to date or tidying into a new method

[VBTroubles]

okay =], sorry when i made this it was such a long time ago and i havnt used vb since then so everything i use to know i pretty much forgot and am trying to learn bit by bit again

[nigelog]

just spotted this

put your corresponding sheet name into each sub
and use this code in each userform

Private Sub UserForm_Initialize()
Dim lrow As Long
Dim rng As Range
With Sheets("Sheet1") ''''''Enter each proper sheetname here
lrow = .Range("a" & Rows.Count).End(xlUp).Row
Set rng = .Range("A1:b" & lrow)
rng.Sort key1:=Range("B1:B" & lrow), _
   order1:=xlAscending, Header:=xlNo
ListBoxA.List = rng.Value '''''change listbox name if necessary
End With
End Sub

that would be the quickest way get you up and running

[nigelog]

drop this code into each userform

Private Sub UserForm_Initialize()
Dim var
var = Right(Me.Caption, 1)
With Sheets("Sheet" & var)
Debug.Print .Name
Dim lrow As Long
Dim rng As Range
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("B1:B" & lrow), _
   order1:=xlAscending, Header:=xlNo
Controls("ListBox" & var).List = rng.Value
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim lrow As Long
Dim rng As Range
Dim var
var = Right(Me.Caption, 1)
With Sheets("Sheet" & var)
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("a1:a" & lrow), _
   order1:=xlAscending, Header:=xlNo
End With
End Sub

I cant test ....workbook will not sit still lol

[VBTroubles]

Attachment 640317

Thanks am trying it now, any reason why its leaving blanks and loading the numbers in so slowly?

and i am having duplicate numbers

[nigelog]

see post 16

[VBTroubles]

nope not working =[

[torachan]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[nigelog]

this is working here

change your sheet item numbering to numbers not a formula. When adding new pattern increment by 1 in the code

I have used for half of the letters with no problem when that is changed

in each userform

Private Sub UserForm_Initialize()
Dim var
Dim arr As Variant
var = Right(Me.Caption, 1)
Sheets("Sheet" & var).Activate
Dim lrow As Long
Dim rng As Range
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("B1:B" & lrow), _
   order1:=xlAscending, Header:=xlNo
arr = rng.Value
Controls("ListBox" & var).List = arr
Set arr = Nothing
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim lrow As Long
Dim rng As Range
Dim var
var = Right(Me.Caption, 1)
With Sheets("Sheet" & var)
lrow = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:b" & lrow)
rng.Sort key1:=Range("a1:a" & lrow), _
   order1:=xlAscending, Header:=xlNo
End With
End Sub

enough from me for now

[VBTroubles]

change your sheet item numbering to numbers not a formula?
how would i go about doing that?

[nigelog]

put a number in column a not a formula reading if column b is populated

[nigelog]

Hope VBTrouble is still around

Did some work on this and using only one userform and passing the search letter via a class module the list box list is now a bubble sorted array as domonic suggested

bubble sort code mike erickson

see attached

[bakerman2]

@ nige

You loaded all Commandbuttons from Findermain into the buttonsgroup where it should only be the alphabet buttons.

Can you test example file for left errors (think it's error free now)

[nigelog]

Hi Bakerman

had just sorted that and the add new pattern code - posted workbook above but I'll be interested in testing your workbook

Edit : seems same method of isolating non alphabetic buttons

Ta Bakerman