How to have an empty combobox when i open a sheet ?

[otrava18]

Hello all,

I have a question for you. I have 10 x activex combobox named from Player1 to Player10. When i open the sheet the CB is already fill with the last value. How can i set all the CB to be empty when i open the sheet ? I find only information about setting ListIndex on -1, but i don`t know how to do that when i open the sheet or if it is a solution for me.

Thank you !

 

[Logit]

.

Paste in a Regular Module :

Option Explicit

Sub ComboStrt()
    Sheets("Sheet1").ComboBox1.Value = Null
End Sub

Paste in the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    ComboStrt
End Sub

 

[otrava18]

Hello ,

I use the first part like this. I`m not sure that it is ok because it doesn`t work. How can i enter the name of the ComboBox ?

Option Explicit

Sub ComboStrt()
    Sheets("Sheet1").Player1.Value = Null
End Sub

 

[Logit]

What IS the name of the combobox ?

 

[otrava18]

What IS the name of the combobox ?

I have 10 ComboBoxes. The name is from Player1 to Player10. Can i set all CB to Null. I’m sorry if i am too noob but i never worked with this. Thank you !

 

[Logit]

.
OK ... previously I suggested pasting code in the ThisWorkbook module. Disregard that completely.

Paste the following in the SHEET MODULE.

Option Explicit

Private Sub Worksheet_Activate()
Dim sht     As Worksheet
Dim oOLE    As Object

Set sht = Sheets("Sheet1")      '<------ Change sheet name here
Application.ScreenUpdating = False
    
With sht
    For Each oOLE In sht.OLEObjects
        If TypeName(oOLE.Object) = "ComboBox" Then
            oOLE.Object.Value = Null
        End If
    Next
End With

Application.ScreenUpdating = True

End Sub

The above macro will clear all comboboxes on the worksheet.

 

[otrava18]

It`s working. You save me a lot of time!

Thank you very much !

 

[Logit]

You are welcome.

Cheers.