default value for combo box

  • Thread starter Thread starter Laurent Payan
  • Start date Start date
L

Laurent Payan

Hello,
I've got 3 combo boxes on the same worksheet, returning
values entered on an other worksheet.
I would like to:
- set up a default value for the 3 combo boxes (blank)
- when one combo box is selected, reset values to default
for the 2 others

Thanks for your help.
 
When do you want the comboboxes to be set to the default value -
Whenver the sheet is entered?
Only when the workbook is opened?

Use the Workbook_Open event would be one approach

Private Sub Workbook_Open()
With ThisWorkbook.worksheets("Sheet1")
.OleObjects("Combobox1").Object.ListIndex = -1
.OleObjects("Combobox2").Object.ListIndex = -1
.OleObjects("Combobox3").Object.ListIndex = -1
End With
End Sub

In the sheet code for the comboboxes

Private Sub Combobox1_Click()
OnlyOne ComboBox1, Me
End Sub

Private Sub Combobox2_Click()
OnlyOne ComboBox2, Me
End Sub

Private Sub Combobox3_Click()
OnlyOne ComboBox3, Me
End Sub



In a general Module
Public Sub OnlyOne(cbox As MSForms.ComboBox, sh As Worksheet)
Dim cb1 As MSForms.ComboBox
Dim cb2 As MSForms.ComboBox
Dim cb3 As MSForms.ComboBox
Set cb1 = sh.OLEObjects("Combobox1").Object
Set cb2 = sh.OLEObjects("Combobox2").Object
Set cb3 = sh.OLEObjects("Combobox3").Object
If cbox.ListIndex = -1 Then Exit Sub
Select Case LCase(cbox.Name)
Case "combobox1"
cb2.ListIndex = -1
cb3.ListIndex = -1
Case "combobox2"
cb1.ListIndex = -1
cb3.ListIndex = -1
Case "combobox3"
cb1.ListIndex = -1
cb2.ListIndex = -1
End Select
End Sub
 
Back
Top