Listbox needed that can be filtered into various smaller lists in userform for MS Word

I have a project whereby I want to show a list of items (about 180) that represent Word shape names or descriptions so that I can select any one of them and have the code create that shape in the Word document. I also want to add option buttons to the userform that can filter that list according to shape categories such as “Basic Shapes”, “Flowcharts”, “Callouts”, etc. (about 10 such filters). I believe I would need a three-dimensional array to populate the listbox, with the first column being the shape name/description, the second the shape category, and the third the VBA shape enumerator, so I can use that in the code that creates the shape in the document (but I want the listbox to show only the name/description). Clicking one of the option buttons would then filter the listbox to show only the shape name/descriptions in that category, and selecting an item will then assign its shape enumerator to a variable that I can then use in the code that creates the shape.

Arrays are great but I am not at all good at creating them, and I’ve been unable to find examples online that I can adapt for this project. Can someone give me a start here?
marrick13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
I think you only need a 2-dimensional array, in fact. The first dimension being 0 to 2 elements in size (your three attributes) and the second dimension being 0 to as many shapes as you want to list.

The fun part is populating the array.. But if you have the data in some sort of list form the following should help.

There are two routines below. They assume that you have a listbox on a UserForm, whose name you are going to pass to the first routine. That listbox should be set up to have three columns, but with the widths of columns 2 & 3 set to zero, so it only shows the shape name

The code works by first splitting your source lists (which must all be the same length) into temporary one-dimensional string arrays. Then it resizes your intended 2D array one "row" at a time to add the data to the three columns. It uses the "Redim Preserve" command to do this. The "Preserve" part is important or the command will lose the array's contents on each "redim" command.

Unfortunately, in VBA you can only dynamically resize the last dimension in an array. So we have to set up the array the wrong way round, with columns where the listbox would expect rows, and vice versa.

Once the array is finished, the code calls the function TransposeArray (designed here only for a 2D array) to swap rows and columns to the right format for the listbox. What's nice is you can then just assign the finished, transposed array directly to the "List" value of your listbox.

Sub PopulateListBox(lb As MSForms.ListBox)
Dim sh As Shape, ss As Selection, wDoc As Document
'constants holding your lists.. or read from a file?
Const sName = "Square,Rectangle,Line,Circle"
Const sType = "Polygon,Polygon,Line,Circle"
Const sNum = "1,1,-2,9" 'numerical equivalents of msoShapeRectangle,msoShapeMixed, and msoShapeOval
'1-d arrays to feed your eventual 2-d array
Dim aName() As String, aType() As String, aNum() As String
Dim iRow As Long

'use Split function to make 1-dimensional arrays from your constants (must be string arrays)
aName = Split(sName, ",")
aType = Split(sType, ",")
aNum = Split(sNum, ",")

'define initial array
Dim aShapes()
'iterate length of array aName.. i loop from 0 to the upper bound of array aName
For iRow = 0 To UBound(aName)
'resize your array:
'in order to be able to resize it dynamically, the resizable dimension must come last
'this is a pain, since the listbox expects the array as eg 0-20 by 0-2 columns
'but we can fix this later... Here each "ReDIm" command resizes the array to one extra "row" of data
ReDim Preserve aShapes(0 To 2, 0 To iRow)
'add data to array
aShapes(0, iRow) = aName(iRow)
aShapes(1, iRow) = aType(iRow)
'convert the string value in aNum to an integer
aShapes(2, iRow) = CLng(aNum(iRow))
Next

'now transpose the array so it is "rows by columns"
aShapes = ArrayTranspose(aShapes)
'attach the result to the listbox.list property
lb.List = aShapes

End Sub


Function ArrayTranspose(a()) As Variant()
Dim aNew() As Variant
Dim iRow As Long, iCol As Long
'create array with inverse dimensions to your existing array
'lBound returns the "lower boundary" of an array's dimension, uBound the upper boundary
'here we swap the second dimension, 2 for the first
ReDim aNew(LBound(a, 2) To UBound(a, 2), LBound(a, 1) To UBound(a, 1))

'run through existing array transposing values
For iRow = LBound(a, 2) To UBound(a, 2)
    For iCol = LBound(a, 1) To UBound(a, 1)
    aNew(iRow, iCol) = a(iCol, iRow)
    Next
Next
ArrayTranspose = aNew
End Function

Open in new window


Hope this helps get you started. The easiest way to filter the resulting list is probably to write another routine that "filters" the array and re-attaches it to the listbox. Let me know if you need help doing that.
marrick13Author Commented:
Neil,

Thanks you very much. I will need more help-certainly with the filtering routine but first: I believe I need to call the 'PopulateListBox' procedure from the Initialize module in the userform, but I do not know how to call a procedure like that if it has parameters.

I am also not sure how to enter the three elements relating to shapes so they are inter-related (like a relational database). I have attached a helper Excel file ('Insert Shape Test Helper') with the data for the listbox. In the Master tab are 'Category' (col C), 'Value' (col F), and 'Listbox Description' (col G), The 'Category' is to be the filtering criteria, which I assume will go into the Const sType, while the 'Listbox Description' will go into the Const sName. But how will this code match the sType with the right sName? If you can give me a small example, that will help - I can usually learn that way.
Neil FlemingConsultant and developerCommented:
Hi Marrick.. I think you forgot to attach the helper Excel file.

Attached is a macro-enabled word document that shows one way to run PopulateListbox

I put a "content control" in the word document and attached this code to the "This Document" code module. There is also a listbox in the form uShapes, and a Module called mTools4Form. It's generally a good idea to put code in a separate module rather than including it in the code for the form itself, especially if you plan to re-use it in other forms.

Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
'macro to handle click of "content controls" in document
With ContentControl
Select Case .Title
 Case "OpenForm": uShapes.Show
End Select
End With
End Sub

Open in new window


That opens the form.. the call to PopulateList is simply placed in the Initialise routine.
Shapes-Form.docm
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

marrick13Author Commented:
Neil,

I have left of the attachment before because the dialog is confusing. I've attached it this time for sure.

Thanks for the form, but I'm having trouble following it. When I click one of the listbox selections, I can't see how it gets the enumerated value for a shape - when I step through the code I don't see it. The code is somewhat over my head, which probably explains why I can't follow it. I'm more accustomed to getting the index of a listbox selection and then using that in a select case routine to look up an associated value. But I'm getting lost in this code.
Insert-Shape-Test-Helper.xlsx
Neil FlemingConsultant and developerCommented:
Ah thanks for the list. I had a similar one here, which I was playing with.

Right now, all the form does is populate the box. However, the enumerated values are in the hidden columns, so easily retrieved using

lb.list(lb.listindex,column)

Open in new window


I have figured out a way to embed your source list in a word document as a so-called "custom XML part" (you can add hidden XML to any Word document), so will upload a new version of the doc shortly with your longer list embedded, plus category filter.

The listbox will read and filter directly from the XML.
Neil FlemingConsultant and developerCommented:
Is the "Seq" value the order you want the items in the listbox?
marrick13Author Commented:
No, I preferred the sequence to be in ascending order on the 'Listbox Description' (col G). I've attached another version of the helper file sorted the way I want (as long as a user can filter by Category and retain the same sorting.
Insert-Shape-Test-Helper.xlsx
Neil FlemingConsultant and developerCommented:
OK. Try the attached. There are three files: one Word, one Excel, one XML. The Excel one I built from your list to test shape-drawing routines, which are subject to the usual Microsoft oddities, as I am sure you have discovered.

I added an extra column though. Microsoft defines shape type as both msoShapeType (high-level types, like Autoshape, or Line) and then as msoAutoShapeType. The latter is the column marked "subType" in the workbook.

(Note for instance that a straight "line" can actually either be a high level shapetype "line" or an "autoshape" of subtype "connector" -- if you draw shapes from the ribbon and select the line with an arrow, Word or Excel will create a connector. But you COULD also create a line with an arrowhead, which will not behave as a connector. This option is not currently in your list, I don't think.)

There are actually more shape types as well than the ones you have listed. See the worksheet "Types" in the Excel file for the full MS list, which includes things like Web Video.
---------
About the files:
The Word file is powered by the XML list of shapes exported to a very basic XML file from the Excel file, and imported manually to Word as an XML part.

To do this import, you need to go to the developer tab and click on "XML Mapping Pane". Then in the dropdown in the pane, click on "Add New Part" and upload an XML file.

Currently you'll see in the list of XML parts, one called "No Namespace" -- this is our list of shapes. If you click on it you will see a long list of XML nodes called "Shape", each with a "Seq", "Category","ShapeName","SubType","Type", and "Description" attached (click the arrow to expand)

Microsoft kindly forgot to provide a way to DELETE custom XML imported into a Word document. So if you want to re-export it from Excel and re-import it, you need to run the macro "DeleteShapeList" first

The Excel workbook exports the XML to the folder named in cell C2, so you would need to change this before testing.

As you'll see I've created some shapes in both the Excel and Word files. The drawing routine is basically the same in both, except that the Excel version takes the shape information from the table, and the Word file uses the custom XML it has embedded in it.

This XML is what feeds the list and the dropdown in the userform.

Function  ReadXMLShapes(sFilter As String) in the module mXMLParts compiles a list of shapes, or a filtered list of shapes, depending on what filter you pass to it.
It turns this list into an array and returns it to the userform, where the array is transposed as before and assigned to the .List property of the ListBox.

I created this listbox (lbShapes) to have six columns, one for each of the columns in the original Excel. If you look at the Properties of the Listbox, you'll see that the widths of these columns are all set to "0pt" apart from the column we want to display, which is column 2 (first column is column ZERO.. so column 2 is actually the third column).Image6.jpg
When you select an item from the list and hit either "Draw Selected Shape" or "Draw at Cursor" the code in module mTools4Shapes reads values from the hidden listbox columns. First it assigns the .Listindex property to a variable iIndex. Then it looks up various columns values at lines such as:
Select Case .List(iIndex, 4)

Open in new window


Based on whether a shape is an "autoshape" (type 1) or not, it then draws the shape. For curves, lines and connectors, as you know VBA uses different shape functions to create the shapes.

And annoyingly, things like whether a connector is curved or not, and the presence of arrows on the ends of lines, are determined at shape creation time. A curved connector is not distinguished by autoshape type from an elbow one. So the code here reads whether the shape should be curved or elbow or straight from the actual description of the shape. Same with arrows.

The Draw Selected Shape button starts drawing at a pre-determined point on the page and then moves right or down for the next shape.

The Draw at Cursor button draws the shape wherever your cursor is positioned (NB immediately after you've opened the form, the cursor is positioned over the "Open Shapes Form" button, so will draw there unless you move it).


Sub DrawSelected()
Dim xStart As Long, yStart As Long
Dim fb As FreeformBuilder
Dim aP() As Single
Dim iConnector As Long, iIndex As Long, iType As Long, iSubType As Long, iPoint As Long


With uShapes.lbShapes
iIndex = .ListIndex
    'abort if no shape selected
    If iIndex = -1 Then
    MsgBox ("Please select a shape")
    Exit Sub
    End If


Set wDoc = ActiveDocument
xStart = 100: yStart = 200
If sX = 0 Then sX = xStart: If sY = 0 Then sY = yStart
    'check fifth column (first column is column 0, so we check column 4) of the listbox for main msoType
    Select Case .List(iIndex, 4)
    'autoshapes
    Case 1
        'normal autoshape, get autoshapetype from column 3 of listbox
        If .List(iIndex, 3) > 0 Then
        Set sh = wDoc.Shapes.AddShape(.List(iIndex, 3), sX, sY, 50, 40)
        End If
        'connectors
        If .List(iIndex, 3) = -2 Then etc etc etc...

Open in new window


When you select a category from the Dropdown, the form re-queries the XML using an expression based on "XPath" notation, to filter it.

For example:
Set xNodes = xRoot.SelectNodes("//Shape[Category='Lines']")

Open in new window


..will get all shapes which have their category set to "Lines"

I don't know if this Word document does what you need it to do, but hopefully the code is helpful in sorting out the array issue and how to get them to work with Listboxes.

If you wanted to build the form into an add-in so you could use it with any Word document, then my XML importing approach will not work very well -- but the code could be re-jigged simply to read from an external xml file on your hard drive (you could also put it on the web), instead of embedding the XML in the Word doc.

Hope all of this helps and is not more confusing than before...
shapeExplore.xlsm
ShapeList.xml
Shapes-Form-v2.docm
marrick13Author Commented:
Wow, Neil -- this is great! You've actually written the whole application for me. I have done a preliminary test and it does almost everything I want it to. The only exception I see up front is that I wanted the option of inserting shapes without their fill. I did not mention this initially in my question as I was asking for help only with the list box elements, but this was one of the factors that motivated me to seek a VBA alternative in the first place, because shapes inserted from the Word interface are filled with blue or purple by default, and their inside text font defaults to white so if you enter text you can't see it unless you change the font color.

I often use shapes to contain text, so those defaults were always a nuisance. I've enclosed a macro I put together that inserts the shapes without the fill and with the text color set to black. It includes an input box for the entry of the shape enumerator (to be used until I had code that you ended up writing so the shapes can be easily selected). I will have to spend some time going through the code to see if I can incorporate these features - ideally, I'd prefer to have the options in the userform of inserting shapes with or without fill and with their inside text black or the Word default. I think the code to control the fill and text font color would go in the 'DrawSelected' module, right?

Much of your code is over my head and I'm not familiar enough with XML to do much with it. By the way, you wrote "Currently you'll see in the list of XML parts, one called "No Namespace" -- this is our list of shapes." But I don't see that "No Namespace" anywhere. Does that matter? I also see that there is a big gap in the 'Select Category' listbox in runtime between the 'Mixed' and 'Rectangles' items (see attached image). How can I remove that gap?

Also, you said "The Excel workbook exports the XML to the folder named in cell C2." And that "If you wanted to build the form into an add-in so you could use it with any Word document, then my XML importing approach will not work very well -- but the code could be re-jigged simply to read from an external xml file on your hard drive (you could also put it on the web), instead of embedding the XML in the Word doc." Well, yes, I want to be able to run this application as an add-on or Word template so I can use it with any Word document, but I don't know how to re-jig the code to accomplish that. I'm afraid this project to too far above my "pay grade" for me to figure that out!

Thanks again for all your help so far - it's great!


Sub Create_Shape_at_Cursor()
Dim oPrintWidth
Dim oShpWidth
Dim oShpHght
Dim oShpTop
Dim oShpLeft
Dim Rectangle As Shape
Dim oDoc As Document
Dim pResult As String

Set oDoc = ActiveDocument
Do
pResult = InputBox("Enter the object number for the desired shape (1-183) and click OK or CANCEL to escape." & vbCr & vbCr _
& "Common shape types are: " & vbCr & vbCr _
& "1- square rectangle" & vbCr _
& "2- parallelogram" & vbCr _
& "3- trapezoid" & vbCr _
& "4- diamond" & vbCr _
& "5- rounded rectangle" & vbCr _
& "6- octagon" & vbCr _
& "7- isosceles triangle" & vbCr _
& "8- right triangle" & vbCr _
& "9- oval" & vbCr _
& "10-hexagon" & vbCr _
& "11-cross" & vbCr _
& "12-pentagon" & vbCr _
& "13-can" & vbCr _
& "14-cube" & vbCr _
& "15-bevel", "Shape Type")
pResult = RealInput(pResult)

 If pResult = "Input canceled by user." Then
   Exit Sub
 End If
Loop Until verifyNumeric(pResult)
If pResult < 1 Or pResult > 183 Then
    MsgBox "Invalid entry.", vbOKOnly, "Invalid entry"
    Create_Shape_at_Cursor
Exit Sub
End If

ActiveWindow.View.Type = wdPageView
ActiveWindow.View.ShowDrawings = True

With oDoc
With .PageSetup
oPrintWidth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
End With

oShpWidth = oPrintWidth
oShpHght = oPrintWidth / 2
oShpTop = 0
oShpLeft = 0

Set Rectangle = oDoc.Shapes.AddShape(pResult, 70, 70, 70, 40)

With Rectangle
.TextFrame.TextRange.Font.ColorIndex = wdBlack 'sets inside text color to black
.Rotation = 0#
.RelativeHorizontalPosition = wdRelativeHorizontalPositionCharacter
.RelativeVerticalPosition = wdRelativeVerticalPositionLine
.Left = oShpTop
.Top = oShpLeft
.Fill.Visible = False 'shuts off fill

End With
End With
With Rectangle.Line
    .ForeColor.RGB = RGB(255, 0, 0) 'red border
 End With

End Sub

Function RealInput(pInput As String) As String
If StrPtr(pInput) = 0 Then
 MsgBox "Input canceled by user.", vbOKOnly, "Cancellation"
 RealInput = "Input canceled by user."
Else
 If pInput = "" Then
   RealInput = ""
   Else
   RealInput = pInput
 End If
End If
End Function

Public Function verifyNumeric(vInput As Variant) As Boolean
  Dim strMsg As String
  If vInput Like "*[!0-9]*" Then
       strMsg = "" & vInput & "' is not a numeric value."
    MsgBox strMsg, vbCritical, "Enter Numeric"
  Else
    verifyNumeric = True
  End If
End Function

Open in new window

Listbox.png
Neil FlemingConsultant and developerCommented:
My pleasure. The fill colour is easily controlled, as you say, in the DrawSelected code. Right now it is using a random fill colour and a random line colour just for demo purposes.

For full portability your best bet might be to revert to what I did in the original demo, which is to put the list of shapes and their MS codes into an actual code module as text constants, which you then split up and use to feed the listbox.

Let me know if you need further assistance.

Sorry for the slow response. Have been away for a week.
marrick13Author Commented:
Neil,

There's no hurry on this...but I am still confused. If by the "original demo", you are referring to the Shapes-Form.docm file for portability, I can see how to populate the three constants with the shape database data, and that that would keep all the data contained in one file. What I can't figure out is how to capture the listbox selection or filter it. You put that together in the second file (Shapes-Form-v2.docm), but that uses external files for the data. I like that form and the way it filters, but I don't have enough VBA expertise to adapt either one into something I can work with. I can create modules with text constants, but I am not sure how to work them in with a listbox that arrays on three elements and have the confidence that the code will take a user selection and capture the right shape type, enumeration, and category. So I need more help from you, such as how to combine the two demos you did into one portable one.
Neil FlemingConsultant and developerCommented:
ah yes.. you're right. We'd have to write a routine to filter the arrays, instead of filtering the XML. That is not hard.. just need to make an array from the arrays. Can you give me a couple of days, though, and I will send you a demo?
marrick13Author Commented:
Sure! No problem -- and no hurry!
Neil FlemingConsultant and developerCommented:
Sorry this took a while. I think this is what you need.

I modified the Excel workbook to output a text file called "shapelist.txt" instead of XML, when you hit the button "Export table as text file". The text file creates pre-formatted "Public" constants for VBA. Don't forget to change the destination folder in Excel cell C2

If you open the text file it spits out, you'll see you can just copy and paste the whole file into the Word VBA code module I added called "mConstants".  I have already done this in the attached, but if you need to recreate the list of shapes because adding to it, then this easily recreates your constants. They need to be "public" so that routines in other modules can read them.

Instead of populating the listbox and dropdown on the form from XML, the userform now calls a routine called "ReadShapeData" in the module "mArrays"

This creates an array from the constants by splitting each of them in turn at each "comma" character, and adding the resulting arrays to a master array of them all called "aShapeData". It then calls another routine called "UniqueValues" to extract the category names for the drop down.

Public aShapeData() As Variant, aCats() As Variant, aSub() As Variant, aLists() As Variant, aListNames() As String

Sub ReadShapeData()
'read shapes for listbox from constants
Dim i As Long, j As Long
Dim a() As String

'don't recreate master array if already exists
If (Not blankArray(aShapeData)) Then Exit Sub

'make array of list names
aListNames = Split(cListNames, ",")
'make array to hold constants converted to arrays (an array of string arrays)
'from constants: cSeq,cCategory,cShapeName,cSubtype,cType,cDescription
ReDim aLists(0 To 5)
aLists(0) = Split(cSeq, ",")
aLists(1) = Split(cCategory, ",")
aLists(2) = Split(cShapeName, ",")
aLists(3) = Split(cSubtype, ",")
aLists(4) = Split(cType, ",")
aLists(5) = Split(cDescription, ",")

ReDim aShapeData(0 To UBound(aLists(0)), 0 To 5)
For i = 0 To UBound(aLists(0))
    For j = 0 To 5
    aShapeData(i, j) = aLists(j)(i)
    Next
Next
a = Split(cCategory, ",")
aCats = uniqueValues(a)
End Sub

Open in new window


When you select a category on the form, the code now runs a routine called "GetSubArray". This runs through the master array and creates a new array based on the parameters in the array variable "aSearch". a ParamArray is a handy way of passing an unknown number of variables to a VBA routine. For now we are actually passing only 2: a column number and a value to match. Like this:
Private Sub ddCategory_Change()
If ddCategory.Value = "*All" Then
lbShapes.List = aShapeData
Else
lbShapes.List = GetSubArray(aShapeData, 1, ddCategory.Value)
End If
End Sub

Open in new window


This tells GetSubarray to look in column 1 of the master array to check if it contains the desired category value.

But if you wanted to add more filters in another dropdown, you could pass 4 values, or 6, or 8... still as pairs of column number and desired value.
Function GetSubArray(aa(), ParamArray aSearch()) As Variant()
Dim iRow As Long, iCol As Long, iSearch As Long, iSize As Long
Dim bb() As Variant, bKeep As Boolean
ReDim b(LBound(aa, 2) To UBound(aa, 2), 0 To 0)
iSize = -1
    For iRow = LBound(aa, 1) To UBound(aa, 1)
        bKeep = True
        'iterate search parameters: column number followed by value (isearch +1)
        For iSearch = 0 To UBound(aSearch) Step 2
        If aa(iRow, aSearch(iSearch)) <> aSearch(iSearch + 1) Then bKeep = False
        Next
    
    If bKeep Then
    'copy row to new array
    iSize = iSize + 1
    ReDim Preserve bb(LBound(aa, 2) To UBound(aa, 2), 0 To iSize)
        For iCol = LBound(aa, 2) To UBound(aa, 2)
        bb(iCol, iSize) = aa(iRow, iCol)
        Next
    End If
Next
GetSubArray = ArrayTranspose(bb)
End Function

Open in new window


Note that the subArray function has to build the sub-array with its dimensions swapped around, since you can only "reDim" the last dimension of an array while preserving the previous contents. It then transposes back the array at the end of the process, so it will sit correctly in the Listbox, calling the routine ArrayTranspose, which is also in the mArrays module.

Hope this all does what you want.
Shapes-Form-v3.docm
ShapeList.txt
shapeExplore.xlsm
marrick13Author Commented:
Neil,

Thank you so much - this looks really good. I can't believe how much work you put into this!

I just played around with it a bit and it seems to be just what I wanted.

It's rather well over my head; I can somewhat follow your explanation, but could never have created this on my own, that's for sure.

I did discover that I can control the formats of the selected shapes in the 'DrawSelected' sub, which is great.

I have two questions:

1. What sequence is the listbox when the 'Select Category' shows "all"? I would expect it to be alphabetical. I have a sort function for listboxes but not sure where I can put it, or whether it would work with this code as a lot of it is unfamiliar.

2. I ran the Word code in a separate folder from the Excel file, and you said to be sure to change the directory in the Excel file. But since the Word code runs fine without it, what is the purpose of the Excel file, anyway?
Neil FlemingConsultant and developerCommented:
1. The sequence on "all" is just the sequence from the Excel file. I actually can't remember why we had it in that order.
2. The excel file is only there to facilitate exporting the constant lists. The word version works fine because I already imported the lists. But if you wanted to change them, the excel tool can re-export them easily. You could sort the shapes in Excel for instance, and re-export as suited you.
marrick13Author Commented:
I see, thanks. I added the following routine to sort the lists, but it doesn't work the way it has in other macros. I put it in Version4 - can you tell me why it doesn't sort the lists?


Private Sub SortList(LBox As String)
Dim aBuf As String
Dim i As Long
Dim j As Long
Dim k As Long

With Controls(LBox)
  k = .ListCount - 1
  For j = 0 To k
    For i = j + 1 To k
      If UCase(.List(i)) < UCase(.List(j)) Then
        aBuf = .List(j)
        .List(j) = .List(i)
        .List(i) = aBuf
      End If
    Next i
  Next j
End With
End Sub

Open in new window

Shapes-Form-v4.docm
Neil FlemingConsultant and developerCommented:
You need to swap each column's value from the list in the sort. Your code only sorts the first column value

Try this. I changed it to pass the listbox directly, rather than its name, and added a parameter for the columns to sort by.

So to call this and sort by name, which is column 2 of the listbox, you need to put
SortList lbShapes, 2

Open in new window

in the initialise and onchange routines.

Private Sub SortList(lb As MSForms.ListBox, iSortCol As Long)
Dim aBuf As String
Dim i As Long
Dim j As Long
Dim k As Long
Dim iCol As Long

With lb
  k = .ListCount - 1
  For j = 0 To k
    For i = j + 1 To k
     'reference both the row and the column in the listbox:
      If UCase(.List(i, iSortCol)) < UCase(.List(j, iSortCol)) Then
       'swap each column value (0 to 5 columns)
        For iCol = 0 To 5
        aBuf = .List(j, iCol)
        .List(j, iCol) = .List(i, iCol)
        .List(i, iCol) = aBuf
        Next
      End If
    Next i
  Next j
End With
End Sub

Open in new window


Hope this helps. I've attached a rewrite.
Shapes-Form-v4.docm
marrick13Author Commented:
This is great, Neil. I think I can "take it from here". Thanks so much!
Neil FlemingConsultant and developerCommented:
It would be appreciated if you would mark my answer as the solution!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrick13Author Commented:
Thanks for all the help!
Neil FlemingConsultant and developerCommented:
My pleasure. Thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
arrays

From novice to tech pro — start learning today.