combobox and listbox questions

[rceonline]

Greetings,

I was able to get my dialog box fully functional but it was more by brute force, not comprehension. I?m hoping I can get some answers so that I can improve my understanding. I started building this dialog box by merging a few examples by John Walkenbach, thank you John.

First, a few contextual points. This dialog is part of a much larger application that allows the user to edit four ranges that are the content of drop down boxes on another sheet whose input range are dynamic named ranges. When I first built the application I would manually edit their content. You will see that 2 of the 4 items available in the combobox allow content in the listbox with columncount = 1 while the other two are columncount = 2. Additionally, the pair of columns for the matrix entry are swapped from their position on the sheet because I use a vlookup in other parts of the application. I think I can replace vlookup with match now that the application is complete.

1) My biggest challenge was in preventing the content of the combobox from changing after edits had been made to the content of the listbox. I need the user to accept their changes before making another selection in the combobox. I have a msgbox display ?You must press update to accept your changes.? I use a module level variable IsDirty as a flag. I tried preventing the change by setting cancel = true in the beforeupdate, change and click events before I tried the entry event. Apparently in the reverse order of how they actually fire.

Question: When I set cancel = true in beforeupdate all the buttons and textbox are disabled but both the combobox and listbox are enabled. Also if I set cancel to be a module level variable I enter an infinite loop in the beforeupdate event to show the msgbox when it was there and not in the entry event. Clicking the close button would end the loop. Does someone know the explanation for these behaviors?

2) I am adding the items to listbox1 with rowsource = ??. I originally added items to the two single column lists with something like:

For i = 1 to nItems
Listbox1.additem = Sheets("tables").Cells(i, col)
Next

Everything worked fine until I went to write the content of the listbox back onto the sheet:

With Sheets("tables")
.Range(.Cells(2, col), .Cells(1 + nItems, col)).Value = listbox1.list
End With

At this point I discovered that the list array was not (nitems, 1) but (nitems, 9); option base 1
Question: Am I correct in assuming that the maximum number of columns in a listbox is 9 and that the default size for the array held by a list box is (listcount, 9)?

3) The user has the option to move items up and down in the listbox. I currently moving each individual item of the list up or down. Question: Is there a way to link the items of a row together? Or is the better solution to use an array to move the items?

4) In my application I create my menus from a table on a sheet. Two of the items have ellipsis. I typed them on the sheet with periods. However the string name for identifying them contains an ellipsis (char(133)). Question: Why is there a conversion and should I use the ellipsis or leave the periods?

[JonPeltier]

1) Sounds like you're requiring too many confirmations. I hate it when I have to approve every stupid thing I want to do. I would let them change whatever they want, and press OK to leave the form and apply all of their changes; if they change their mind, I let them cancel out of the form and the new information is discarded.

2) I don't know what you mean by your "(nitems, 1)" terminology. The limit on listbox columns is not 9. I don't know what it is, but I just defined one with 12 columns. More than that is probably not going to render legibly, unless several of the columns are hidden. There is no default size for the array of listbox values; this depends on what you fill the listbox with. A listbox is option base 0. If you have 10 list items the listbox' array goes from list(0) to list(9). If you transfer an array to the sheet, the top left array element goes into the top left cell, regardless of the array indices of the first array element.

BTW, when I work with arrays, I never rely on Option Base. I always provide both lower and upper bounds:

Dim MyArray(1 to 5, 3 to 8)

This allows me to more easily relate an array to a worksheet range; the above example can simplify working on rows 1-5 and columns 3-8 (C to H).

3) I use an array, but you could just treat the listbox the same way, and swap the pairs in each column. They aren't linkable in the way you are asking.

4) AutoCorrect changes a lot of things, three periods to a single character ellipsis being one of them, also (c) to ? and others. You can delete the ... to … change in the AutoCorrect dialog (Tools menu > AutoCorrect Options...). This is one that I usually delete, because to me the ellipsis doesn't look like three periods.

[timothysteel]

Hello...

I have created (Excel 2003) several drop down list boxes within cells in a spreadsheet...however the font size within the drop down is almost to small to read. Are there any controls over the font size in a drop down list box? If you have the answer please e-mail me.

Thanks.

timothy@steelt.com

[JonPeltier]

There's not much you can do for a forms toolbar combobox, other than keep the zoom around 100%.

A controls toolbox combobox can be formatted in many ways. Go into design mode, then right click on the control and choose Properties.