Default Value in Combobox

Is there a way to set a default value for a combobox (or ALL comboboxes on a worksheet) so that it is automatically set to that value when the file is opened?

Thanks for your help.

Hello VitoHGrind,

This macro will set the each Drop Down in the entire workbook to the first item in its list. You can place a call to this macro in either an Auto_Open procedure or the Worksheet_Open event procedure.

Sub SetDefaults()

  Dim DD As Excel.DropDown
  Dim Wks As Worksheet
  
    For Each Wks In ThisWorkbook
      If Wks.DropDowns.Count > 0 Then
        For Each DD In Wks.DropDowns
          DD.ListIndex = 1
        Next DD
      End If
    Next Wks
    
End Sub

Sincerely,
Leith Ross

Hi Leith thanks for your code. However, I can't get it to work. I think its because I'm using Comboboxes and not dropdowns.

I tried to modify your code some with some you posted earlier. so now it looks like:

Sub SetDefaults()

  Dim cmb As MSForms.ComboBox
  Dim obj As Object
  Dim Wks As Worksheet
  
    For Each Wks In ThisWorkbook
        For Each obj In ActiveSheet.OLEObjects
            Set cmb = obj.Object
            If TypeName(cmb) = "ComboBox" Then
                cmb.ListIndex = 1
            End If
        Next obj
    Next Wks
    
End Sub

Private Sub Workbook_Open()
    SetDefaults
End Sub

I also tried:

Sub SetDefaults()

  Dim obj As Object
  Dim Wks As Worksheet
  
    For Each Wks In ThisWorkbook
        For Each obj In ActiveSheet.OLEObjects
            If TypeOf obj.Object Is ComboBox Then
                obj.ListIndex = 1
            End If
        Next obj
    Next Wks
    
End Sub

But I can't get either to work.

I also tried taking away the wks refefernces (leaving just 'For each obj.. next obj') and using Auto_Open instead of Worksheet_Open but none of that works either.

Any suggestions?

Hello VitoHGrind,

If you could post your workbook that would be best. At this point, I don't have any ideas what may be the problem.

Sincerely,
Leith Ross

I think I figured out what part of the problem was. I was placing that code on the worksheet and not in a module.
I moved the code to a module and it worked (sort of), but it took over an hour for the workbook to load.

When it did load, the boxes were set to the second item and not the first. I needed to use:

cmb.ListIndex = 0

I decided to move the action call to a commandbutton and it works great. Thanks. Eventually I had to use

For Each obj In ActiveSheet.OLEObjects
     If TypeOf obj.Object Is ComboBox Then
          Set cmb = obj.Object
          cmb.ListIndex = 0
     End If
Next obj

I don't know if I need to open a new thread, but I'd also like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :

cmb.LinkedCell.value = vlookup(cmb.value, LookupList,2)

Again this will be a global and apply to every box in the worksheet.

Any thoughts?
Here's part of the file. I had to recreate, take some of the more conf. stuff out and I've also turned auto-calculate off to meet the size req.