List all sheets of opened workbook into ComboBox

[tmatematikas]

Hello everybody. I have such an isue: I'm tryin to list all sheets into ComboBox with VBA, here is the code:

Function ListSheets()
    Dim WS As Worksheet
    For Each WS In Worksheets
        ComboBox1.AddItem (WS.Name)
    Next WS
End Function
 
Function ClearSheets()
    Dim i As Integer
    Dim n As Integer
    n = ComboBox1.ListCount
    For i = n To 1 Step -1
        ComboBox1.RemoveItem (i)
    Next i
End Function
 
Private Sub ComboBox1_DropButtonClick()
    ClearSheets
    ListSheets
End Sub

Unfortunately, there comes out some kind of error (invalid argument) and I can not take ComboBox' text value to cell inside change() method.
If I change one method to as follows:

Private Sub ComboBox1_DropButtonClick()
    'ClearSheets
    ListSheets
End Sub

then the sheets are listed, but every time I click the ComboBox' button all sheets names just appends in the combo box. I've tried ComboBox1.Clear, but, situation is the same then: an error occurs. Any suggestion how to clear that ComboBox and fill again? Maybe that's something wrong with this code?

[Demosthine]

Good Morning.

You're code to add the list of Worksheets to the ComboBox is correct. Where your error comes in is during your ClearSheets procedure, which you seem to gather.

As your code is written, you are clearing an item from the ComboBox with each execution of the Loop. However, once you delete that item, the entire ListCount and ListIndex is changed.

------------------------------------------------------------------
Index - ComboBox Contents
1 - Sheet1
2 - Sheet2
3 - Sheet3

- RemoveItem 1
Index - ComboBox Contents
1 - Sheet2
2 - Sheet3

- RemoveItem 2
Index - ComboBox Contents
1 - Sheet2

- RemoveItem 3
Error, there is no Item 3!
------------------------------------------------------------------

This is a really easy fix. In your ClearSheets procedure, replace all of your code with one line. I know you said you tried it, but that's the solution. It may be how you used it. Attached is a working example that I tested.

ComboBox1.Clear

Good Luck.
Scott

[Norie]

Why are you using Functions for this?

It's not the sort of thing they are intended for.

Also why use one of the combobox's own events to populate it?

If the combobox is on a userform try this.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ComboBox1.AddItem ws.Name
    Next ws
End Sub

[tmatematikas]

But I think, I am removing those items from the end:

For i = n To 1 Step -1

Clear method gives the same error.
I'm using vba, because I have some sheets with random matrices (30x500, 30x2000, ..., 30x32000) and such list of available sheets is useful, I can have 1 calculation sheet for separate n sheets at a time.

[Demosthine]

Hey there.

I'm sorry, you are removing them from the end, so I'm not quite sure why it would give you an error. If you post your book, we can look at the actual code and give you a better solution.

Scott

[tmatematikas]

Ok, there is "lite" version of my book:

http : //tmatematikas.mikrovisata.net/ dokumentai/ ComboBox.xls

[Demosthine]

Hey there.

I'm not sure why I didn't notice it before, but the error is coming up because of your Index. A ComboBox uses ListIndex 0 - n. You have to run your loop as For i = ComboBox1.ListCount - 1 to 0.

That should fix your problem.
Scott

[tmatematikas]

Oh yes, it now works fine . Such a spastic error. It's about time to mark this as solved, I think.

[lucas]

tmatematikas

You can mark your own thread solved using thread tools at the top of the page. I will mark this one solved for you and I am also going to move it to the Excel help forum so others can benifit from your problem