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, 6 views)

This userform is not how I would do this layout at all. It would make a LOT more sense to do the names as entries in a listbox with a couple of buttons and text fields to allow you to edit any of the list positions. A list in a listbox is already an array so weeding out blanks, editing, sorting and returning the results becomes simple.

Can I ask why you chose buttons and captions for what appears to be fields?

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

This is part of an AutoText Tool I developed (as an adjunct to Word's autotext feature) that allows one to enter data in any of three lists and choose them to be written to the document. I recently added a toggle function whereby one can select ant two items from any of these lists and click a Toggle button. The macro would then alternate writing those selections to the document just by clicking that button.

However, the toggle is limited to the last two selections -- good if one wants only to alternate writing two pieces of text to a document (such as for a script or screenplay with only two character names to precede their dialog). But as I wanted to be able to click a button to write up to ten pieces of text (or character names) without having to select them from any of the listboxes, I hit upon the idea of setting up a separate form with the ten labels you see, and an Edit and Delete command for each. The idea was to allow one to click an Edit button, which would open an input box, type or paste in the text, and do this for however many pieces of text he wants to have so he can just click the button and the application would write its caption to the document. But I wanted to be able to sort those labels alphabetically (to make the text easier to find while typing and clicking the buttons), and thought that the only way to do that was to put the captions in an array, sort the array, and then have the application apply the ordered captions to the labels (instead of actually moving the labels, which was my first thought). I thought that the alphabetical sorting function would be the hardest to achieve, and I was right. But I do not want a listbox because I already have that in the main application (the three listboxes); for this, I wanted to be able to write up to ten pieces of text to the document by just clicking a button. Imagine a script or interview where one has to repeat several speaker names in a document. If there are more than two, the toggle won't be adequate for this, and having to find and select them from a listbox adds mouse action and time. But if I want to be able to choose from among up to ten pieces of text and continually repeat those pieces of text by just clicking a button, this is what I came up with.

The multiclickwrite form I shared on the forum is only part of this AutoText Tool and I wanted to get it working separately before adding it to the main application.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Greg,

Your code is wonderful -- just what I wanted. Thanks so much! I never thought of using a collection, or putting controls into it, but then, I'm not a developer, either (and rather poor at constructing arrays, even though they are quite powerful).

Thank you again -- I really appreciate your help!

You are welcome.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/