ComboBox clear question

[ipbr21054]

Afternoon,

When i open my worksheet my last search is still shown in the Combobox.
I would like it to be clear with nothing shown when i open my worksheet etc.

I have tried this code below but i see a error message object does not support this method.
Debuge tells me the line not supported is With ThisWorkBook etc

Please can you advise the correct way to clear upon opening.

Private Sub Workbook_Open()With ThisWorkbook.Sheets("DATABASE").ComboBox1
.SelectedIndex = -1
End With
End Sub

Have a nice day

 

[ipbr21054]

I made a mistake in that i put the code on the sheet as opposed to the Thisworkbook etc.

I have now placed the code below there but still get does not support this method

Private Sub Workbook_Open()With ThisWorkbook.Sheets("DATABASE").ComboBox1
.SelectedIndex = -1
End With
End Sub

 

[My Aswer Is This]

Try:
ComboBox1.ListIndex = -1

 

[ipbr21054]

@MAIT
Currently i am doing the same as your image on your profile.

Using the code below i see this message, Variable not defined

Private Sub Workbook_Open()With ThisWorkbook.Sheets("DATABASE").ComboBox1
ComboBox1.ListIndex = -1
End With
End Sub

 

[ipbr21054]

So it has something to do with Option Explicit at the top,so now need to see why ??

 

[My Aswer Is This]

I failed to notice you were using With.

Try this:

With ThisWorkbook.Sheets("DATABASE").ComboBox1
.ListIndex = -1
End With

 

[ipbr21054]

OK,
So i now have the code in use shown below. BUT i see a new message Object doesnt support this property or method.
Debug shows this in yellow With ThisWorkbook.Sheets("DATABASE").ComboBox1

Private Sub Workbook_Open()With ThisWorkbook.Sheets("DATABASE").ComboBox1
.ListIndex = -1
End With
End Sub

 

[My Aswer Is This]

I tested this and it works for me.

Trying testing the code in a Command Button and not on Workbook Open and see if it works.

I always test script first before putting them in a Workbook Open script.

And I would think it should look like this:

Private Sub Workbook_Open()
With ThisWorkbook.Sheets("DATABASE").ComboBox1
.ListIndex = -1
End With
End Sub

 

[ipbr21054]

Hi,

This now works.

Private Sub Workbook_Open()With ThisWorkbook.Sheets("DATABASE").ComboBox1
.ListIndex = -1
End With
End Sub

I have noticed that all the names in the combobox are gone BUT if i type say TOM,and there are 2 TOM in my database it only returns one in the combobox.

I then tried the same with TEST where i have say 10 it only return 1.

I then tried STEVE where i have 5, it also return 1

It will return only the first name it matches whether there is 2 or 30

Did i start to ask for the wrong thing selectindex-1 / ListIndex = -1 ???

I did have a whole combobox list of names,i would type STEVE JONES,close the workbook etc & upon opening STEVE JONES would still be there.
I was just trying to have nothing shown.

If this is an issue i think i need to look at other options,can you suggest anything

 

[My Aswer Is This]

Well to start with you earlier said my script did not work now you say it does.
So not sure why this change.

A combobox or listbox do not retain their values when the workbook is closed.
So they have to be reloaded each time the workbook is opened.

How are you loading the names into the listbox?

You cannot just enter Steve into a listbox and expect all the Steves to be loaded into a listbox.
Unless you have some script doing this.

It may help to explain in detail what your trying to do.