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

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

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

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