asked on

Need advice for VBA combobox: How do I set a combobox to a default value?

Hi

I've come across following problem. I need to populate a combobox ("Abteilungen") with data from an Excel spredsheet. The combobox is in a Word userform. This is my code:

Private Sub AlleBetriebe()
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim Listarray As Variant
Dim bStartApp As Boolean
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
  bStartApp = True
  Set xlApp = New Excel.Application
End If
On Error GoTo 0
With xlApp
  Set xlbook = .Workbooks.Open("IGA.xlsm")
  Listarray = xlbook.Names("Abteilungen").RefersToRange.Value
  xlbook.Close SaveChanges:=False
  Set xlbook = Nothing
End With

If bStartApp Then xlApp.Quit
Set xlApp = Nothing
With cboBetrieb
  .ColumnCount = UBound(Listarray, 2)
  .Clear
  .List() = Listarray
End With
Set xlApp = Nothing
Set xlbook = Nothing

End Sub

Open in new window

The code returns the following values:

BüroService
HR Services
Intake
JobCoaching

I do, however, already have a value/variable which I would like to have set as a default.
The name of that variable is txtBetrieb.

Where and how do I set the default value?

Please let me know if you need the original files.

Massimo

ASKER CERTIFIED SOLUTION