I am trying to arrange 10 label controls on a userform alphabetically (ascending or descending) according to what a user would enter in the captions. The attached form shows the labels and has the code I’ve been working with.



The code loops through the form’s controls, identifies the ‘label’ controls, and enters their captions in an array. This seemed to work well when I was just displaying the results in a msgbox, but when I tried to add code that sorts the array alphabetically so the macro could then change the label captions to display in alpha order, this works the first time the Sort function is run when the form opens, but after that, changing the sort option (ascending to descending or vice versa) in the same userform session skews the results, adds spaces between the array elements, and generally becomes a mess.

I thought this might be due to values of variable or objects carrying over from the previous Sort run, but every time I’ve set them to zero, nothing, or ““, I get the same skewed results.

While preparing this version for the Forum, I was getting an error on the ‘If frmMultiClickWrite.LblLabel2.Caption <> ““ Then frmMultiClickWrite.LblLabel2.Caption = MyArray(2 - 1)’ statement in the ‘Sort’ module. But now it sometimes produces the “An error occurred while loading this document” message, which means I have to shut down Word. This is a new error but with my limited VBA experience, I cannot look at the code and determine why this is happening, or why the resulting msgbox text and label captions worked on the first try and not afterwards, or why I’m not getting the “An error occurred while loading this document” error. Can anyone help me out?

Userform and code are attached.

Attached Files

Sort Userform Labels Using Array Data.docm (36.5 KB, 8 views)