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?