Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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
0
Massimo Scola
Asked:
Massimo Scola
2 Solutions
 
byundtCommented:
I had trouble with your statement Set xlApp = New Excel.Application

After commenting that out, the following code worked with Excel already having been launched:
Private Sub AlleBetriebe()
Dim xlApp As Object
Dim xlbook As Object
Dim Listarray As Variant
Dim bStartApp As Boolean
Dim txtBetrieb As String
txtBetrieb = "c" 'Default value
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 ThisDocument.cboBetrieb
  .ColumnCount = UBound(Listarray, 2)
  .Clear
  .List() = Listarray
  .Value = txtBetrieb
End With
Set xlApp = Nothing
Set xlbook = Nothing

End Sub

Open in new window

0
 
SiddharthRoutCommented:
NOT FOR POINTS

mscola: I believe you are Early Binding with Excel. There is nothing wrong with your Early binding code but I would suggest that you adopt Late Binding as Brad has suggested as the end user will face problem if he or she doesn't have the same Excel version as yours.

A slight amendment to late binding code. This takes care if Excel is not launched already.

    Dim xlApp As Object
    Dim xlbook  As Object
        
    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    
    '~~> If not found then create new instance
    If Err.Number <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

Open in new window


Sid
0
 
Massimo ScolaAuthor Commented:
Thanks a lot guys!
You helped me a lot. I really appreciate your help.

Massimo
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now