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

otrava18

New Member
Joined
Feb 11, 2018
Messages
36
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 !
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,093
.

Paste in a Regular Module :

VBA Code:
Option Explicit

Sub ComboStrt()
    Sheets("Sheet1").ComboBox1.Value = Null
End Sub
Paste in the ThisWorkbook Module:

Code:
Option Explicit

Private Sub Workbook_Open()
    ComboStrt
End Sub
 

otrava18

New Member
Joined
Feb 11, 2018
Messages
36
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 ?

VBA Code:
Option Explicit

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

otrava18

New Member
Joined
Feb 11, 2018
Messages
36
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,093
.
OK ... previously I suggested pasting code in the ThisWorkbook module. Disregard that completely.

Paste the following in the SHEET MODULE.


VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,727
Messages
5,446,167
Members
405,388
Latest member
Arlind

This Week's Hot Topics

Top