rceonline
01-06-2007, 10:42 AM
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.
:dunnoQuestion: When I set cancel = true in beforeupdate all the buttons and textbox are disabled but both the combobox and listbox are enabled. :dunnoAlso 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
:dunnoQuestion: 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. :dunnoQuestion: 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)). :dunnoQuestion: Why is there a conversion and should I use the ellipsis or leave the periods?
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.
:dunnoQuestion: When I set cancel = true in beforeupdate all the buttons and textbox are disabled but both the combobox and listbox are enabled. :dunnoAlso 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
:dunnoQuestion: 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. :dunnoQuestion: 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)). :dunnoQuestion: Why is there a conversion and should I use the ellipsis or leave the periods?