I have an Excel sheet for data input by a third party. To restrict erroneous data I want to restrict data to a certain list of items.
As the list is rather long, I want to use a combobox, so that when they start typing the correct names show up.
This worked in testing, but as soon as I shipped the Excel file the ActiveX Combobox started acting out. This appears to be known behaviour, without a fix besides not using the ActiveX version of the combobox.
I'm switching to make use of the Forms version of the combobox.
Besides the combobox there is a simple cell with data validation with two options: "Professioneel" and "Particulier". I use an onchange event to capture if the selection changes, and then I populate my combobox with the corresponding list.
This worked with the ActiveX version, but nor the forms version.
The code below is my latest try at populating the forms combobox.
The Else clause of the If statement still shows the code that used to work for the ActiveX version of the combobox.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C7")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Range("C7").Value = "Particulier" Then
ThisWorkbook.Worksheets(4).Shapes("ComboBox1").List = ThisWorkbook.Worksheets(8).Range("B2:B58").Value 'this does not work
Else
ComboBox1.List = ThisWorkbook.Worksheets(7).Range("B2:B434").Value 'this used to work for a combobox
End If
End Sub
I get error 438.
How do I get this to work with a Forms version of the combobox, or is this behaviour not supported in the forms version?
