CODEWITHSUNDEEP
vbaexcelcombobox
QHarr
QHarr

Reputation: 84465

VBA: ComboBox only showing one item after Workbook_Open event

I am attempting to have a Workbook_Open event populate a controls ComboBox so that when the user goes to the Worksheet("Benchmarking"), they have a pre-populated list to choose from that includes all the items in the array datesArr.

The problem i am having is, upon opening the spreadsheet and navigating to the Worksheet("Benchmarking"), i am only seeing one item in the drop down list:

Current situation with ComboBox1

If i select that item then the list actually populates:

Full list appearing when selecting initial single item

Desired result:

I want the full list to be available from the first time the user tries to make a selection not just after the ComboBox1_Change event is fired.

Having reviewed numerous post e.g. Sometimes the ActiveX Combobox only shows one row, why? , Populating Combo Box on WorkBook Open I have tried several different approaches including the following in the Workbook_Open event code:

.ListFillRange = "DropDownDates"  
.List = DateArrToStrAr

I have also looped the array adding the items to ComboBox1. Each time i get the same 1 visible item in drop down result.

Is someone able to tell me where i am going wrong please?

My current code is

1) ThisWorkbook 

Private Sub Workbook_Open()

    With Worksheets("Benchmarking").OLEObjects("ComboBox1").Object
        .Clear
        .List = DateArrToStrArr '         
    End With

End Sub

2) Worksheet("Benchmarking"):

Private Sub ComboBox1_Change() 'QH 2/11/17

     Dim datesArr() As String
     Dim ws As Worksheet

     Set ws = ThisWorkbook.Worksheets("Lkup")

     datesArr = DateArrToStrArr 'function that reads a named range of dates and converts to string to avoid dd/mm becoming mm/dd

     If ComboBox1.Value = vbNullString Then ComboBox1.Value = "01/04/2016"

     ComboBox1.List = datesArr

     '.....other code

End Sub

Notes:

The array datesArr is populated by the function DateArrToStrArr() which reads in a named range of dates "DropDownDates" (workbook scope) and converts them to a string array. This is then assigned to the ComboBox.

DropDownDates is a dynamic named range with formula =OFFSET(Lkup!$F$16,,,Lkup!$M$12,)

Set-up: Excel 2016 64 bit Windows.

Upvotes: 2

Views: 1301

Answers (1)

QHarr
QHarr

Reputation: 84465

Thanks to @CLR for making me think about recalcs. I decided to hack my way around this with the following:

I have added in Worksheet("Benchmarking") a Worksheet_Activate event and removed the Workbook_Open code. This seems to do the trick

Private Sub Worksheet_Activate()
  ' ComboBox1.Clear
   ComboBox1.List = DateArrToStrArr
End Sub

Upvotes: 1

Related Questions