ComboBox populating data from closed Excel

[mfrela]

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

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

 

[Norie]

Why are you populating the combobox then removing items from it?

 

[mfrela]

Hi there

if I don't clear CB than I have stack of repeated data in CB. So everytime I open CB with arrow I get data + data one again (in stack)...
That's why I decided t clear all data everytime I open CB.

Is there a better way? Some mistakes in my code? Have you any idea how to solve my problem?

Thanks!

 

[Norie]

Don't use AddItem to populate the combobox use List.

Me.ComboBox1.List = ListItems

You might want to consider using a different event to populate the combobox.

Where is it located?

 

[mfrela]

Thank you! It's working really well! Thanks!

My list is located on my servers starting with "\\sro010\templates\Names.xlsx"

----------------------------------------------------------------------------

I have another question if you are willing to help me out.

I'm trying to use "MonthView" and I would like to calendar to pop up when I click specific cell (or cells). And I would also like to position of the calendar below the clicked cell.

I was trying to do that with this code but no success...

Private Sub MonthView1_Click()
    ActiveCell.Value = CDbl(MonthView1.Value)
    ActiveCell.Select
    MonthView1.Visible = False
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
    MonthView1.Visible = False
    Exit Sub
    End If
    If Not Application.Intersect(Range("E12, G12, E14, G14, E16, G16"), Target) Is Nothing Then
        MonthView1.Left = Target.Left + Target.Width - MonthView1.Width
        MonthView1.Top = Target.Top + Target.Height
        MonthView1.Visible = True
        MonthView1.Value = Date
    ElseIf MonthView1.Visible Then MonthView1.Visible = False
    End If
End Sub

And if I click on cell I can exit with "ESC"

Private Sub ell_KeyPress(KeyAscii As Integer)  If Chr(KeyAscii) = "{ESC}" Then
     MonthView1.Visible = False
  End If
End Sub

Can you have any idea?