Consulting

Results 1 to 9 of 9

Thread: List all sheets of opened workbook into ComboBox

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    List all sheets of opened workbook into ComboBox

    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?
    Last edited by tmatematikas; 09-28-2008 at 10:17 AM. Reason: To make text simplier to read

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •