Hi to all
I have Combo box (CB) which populates data (list of names) from other Excel file (on server). It's working fine but the problem occurs when I click directly on the arrow of CB - I get only first data populated.
But if I click firstly in the CB and then on CB arrow all data (list of names) is populated.
There are picture enclosed.
- first click directly on CB arrow: https://www.dropbox.com/s/72r8zikyspwdkmo/Combobox_2.jpg?dl=0
- click on arrow after in Combobox click: https://www.dropbox.com/s/kcgeakf473xfrhx/Combobox_3.jpg?dl=0
Can anyone help me with that? Code is below...
Thank you in advance.
Regard, Frella
I have Combo box (CB) which populates data (list of names) from other Excel file (on server). It's working fine but the problem occurs when I click directly on the arrow of CB - I get only first data populated.
But if I click firstly in the CB and then on CB arrow all data (list of names) is populated.
There are picture enclosed.
- first click directly on CB arrow: https://www.dropbox.com/s/72r8zikyspwdkmo/Combobox_2.jpg?dl=0
- click on arrow after in Combobox click: https://www.dropbox.com/s/kcgeakf473xfrhx/Combobox_3.jpg?dl=0
Can anyone help me with that? Code is below...
Thank you in advance.
Regard, Frella
Code:
Private Sub ComboBox1_Gotfocus()
Dim ListItems As Variant, i As Integer
Dim r As Integer
Dim SourceWB As Workbook
With Me.ComboBox1
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("\\name025\predloge\Names.xlsx", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("E2:E200").Value
SourceWB.Close False
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems)
For i = 1 To UBound(ListItems)
.AddItem ListItems(i)
Next i
For r = .ListCount - 1 To 0 Step -1
If .List(r, 0) = "" Then
.RemoveItem r
End If
Next r
.ListIndex = -1 ' no items selected, set to 0 to select the first item
Application.ScreenUpdating = True
End With
End Sub