Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reload Listbox - type mismatch error
I've created to arrays that hold the sheet names for a worksheet,
mySheetList() which holds the natural sheet order and mySheetListAlpha() which holds a the sheet names alphabetically ordered. I want to offer the user a form containing a listbox with the sheetnames and two buttons on the form that the user select either natural order or alphabetically ordered. By clicking on the buttons, the listbox is dynamically updated with either one of the two arrays. I call a list box via UserForm_Initialize() and populate the list with: ListBox1.List = mySheetList This part works well (I can also get the listbox populated alphabetically with ListBox1.List = mySheetListAlpha). My problem lies in trying to update the listbox with the second array. On the form is a command button with the following code Listbox.Clear For X = LBound(mySheetListAlpha) To UBound(mySheetListAlpha) ListBox1.AddItem mySheetListAlpha(X) Next The listbox.clear command clears the listbox but the second line of code causes Type Mismatch error. Can someone tell me how I can repopulate the listbox with values held in the second array. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reload Listbox - type mismatch error
Hi Stu,
If I guess correctly you are assigning your values to the arrays "mySheetList " and "mySheetListAlpha" directly from worksheet ranges. If that is the case even if you choose a unidimensional range you get a bidimensional array. However, while the method List takes care of the multiple dimensions of the array, the AddItem method needs to be told explicitly which row and column you want to add. So if your "mySheetListAlpha" array only has one column of data, you could use the following code: Private Sub CommandButton1_Click() mySheetListAlpha = ActiveSheet.Range("B1:B7") ListBox1.Clear For X = LBound(mySheetListAlpha) To UBound(mySheetListAlpha) ListBox1.AddItem mySheetListAlpha(X, 1) Next End Sub Private Sub UserForm_Initialize() mySheetList = ActiveSheet.Range("A1:A10") ListBox1.List = mySheetList End Sub Regards, KL "Stu" wrote in message ups.com... I've created to arrays that hold the sheet names for a worksheet, mySheetList() which holds the natural sheet order and mySheetListAlpha() which holds a the sheet names alphabetically ordered. I want to offer the user a form containing a listbox with the sheetnames and two buttons on the form that the user select either natural order or alphabetically ordered. By clicking on the buttons, the listbox is dynamically updated with either one of the two arrays. I call a list box via UserForm_Initialize() and populate the list with: ListBox1.List = mySheetList This part works well (I can also get the listbox populated alphabetically with ListBox1.List = mySheetListAlpha). My problem lies in trying to update the listbox with the second array. On the form is a command button with the following code Listbox.Clear For X = LBound(mySheetListAlpha) To UBound(mySheetListAlpha) ListBox1.AddItem mySheetListAlpha(X) Next The listbox.clear command clears the listbox but the second line of code causes Type Mismatch error. Can someone tell me how I can repopulate the listbox with values held in the second array. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reload Listbox - type mismatch error
I just knocked this up and it works fine, as many times as you want
Dim mySheetListAlpha Dim mySheetList Private Sub CommandButton1_Click() Dim X As Long ListBox1.Clear For X = LBound(mySheetList) To UBound(mySheetList) ListBox1.AddItem mySheetList(X) Next End Sub Private Sub CommandButton2_Click() Dim X As Long ListBox1.Clear For X = LBound(mySheetListAlpha) To UBound(mySheetListAlpha) ListBox1.AddItem mySheetListAlpha(X) Next End Sub Private Sub UserForm_Activate() mySheetList = Array("Data", "Summaries", "Alpha_data", "Gamma_data", "Beta_data") mySheetListAlpha = Array("Alpha_data", "Beta_data", "Data", "Gamma_data", "Summaries") End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Stu" wrote in message ups.com... I've created to arrays that hold the sheet names for a worksheet, mySheetList() which holds the natural sheet order and mySheetListAlpha() which holds a the sheet names alphabetically ordered. I want to offer the user a form containing a listbox with the sheetnames and two buttons on the form that the user select either natural order or alphabetically ordered. By clicking on the buttons, the listbox is dynamically updated with either one of the two arrays. I call a list box via UserForm_Initialize() and populate the list with: ListBox1.List = mySheetList This part works well (I can also get the listbox populated alphabetically with ListBox1.List = mySheetListAlpha). My problem lies in trying to update the listbox with the second array. On the form is a command button with the following code Listbox.Clear For X = LBound(mySheetListAlpha) To UBound(mySheetListAlpha) ListBox1.AddItem mySheetListAlpha(X) Next The listbox.clear command clears the listbox but the second line of code causes Type Mismatch error. Can someone tell me how I can repopulate the listbox with values held in the second array. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reload Listbox - type mismatch error
Thanks Bob - the problem was that the Dim statements for arrays were in
a Private Sub. Once I moved them to the declarations section the module the code worked fine. Cheers, Stuart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
type mismatch error | Excel Programming | |||
Type mismatch error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Type Mismatch error | Excel Programming |