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]

The values are from a names range 7 when the workbook is opened clicking on the combobox arrow shows all the names.

If i type Steve & close the workbook then re-open the workbook Steve is still shown.
I was looking for it to show nothing,i suppose to reset the combobox as if nothing was typed as opposed to keep seeing previous names from the last person who used it.

If this is not possible then can we somehow have a default name so when it opens it will always show this name as opposed to Steve smith,Paul Brown etc etc.

If that is not possible or takes up to much time then we can leave it & just put up with it.

Thanks for your time with this.

 

[My Aswer Is This]

If you just type Steve into the Listbox then it's not really loaded in it's just sitting their.

I did not understand your answer when I asked what values are loaded into the listbox.
You said:

The values are from a names range 7

What does that mean?

Do you mean a Named range named 7

I do not believe you can have a named Range named 7

You said:

clicking on the listbox arrow shows all the names.

Are you saying when You open the workbook the name steve is still in the combobox but when you click on the Combobox arrow now all the names are entered?

Show me the script that loads all the values into the listbox

You know what I mean. I may be mixing up terms here listbox combobox
​

​

 

[ipbr21054]

I dont know where that 7 came from but Yes its a named range & that is where all the names come from to be seen in the combobox.

Yes,when i open the workbook the last typed entry is still shown.
When i said i click the down arrow i was refering to all the names are loaded for the combobox.

Basically whether you close the workbook or open the workbook whatever is shown in the combobox needs to be cleared so the space where you type is clear.
At present lets say steve smith is shown,i need to back space to remove it then type say My Answer Is This.
If i then close the workbook down then re-open it My Answer Is This is still there.

I think i will leave this as its taken up to much of our time & should be easy.
Many thanks for your time anyway.

 

[My Aswer Is This]

Do you want this named Range loaded into the combobox when the Workbook is opened?

What is the name of the named range?

 

[My Aswer Is This]

If you do not want that named range entered on workbook open
Then try this

On workbook open use this:
Combobox1.value=""

 

[ipbr21054]

I could try that i suppose if you dont mind.

Its called SalesReturns

 

[My Aswer Is This]

Try this:

Private Sub Workbook_Open()
With ThisWorkbook.Sheets("DATABASE").ComboBox1
.Value = ""
.List = Range("SalesReturns").Value
End With
End Sub

 

[ipbr21054]

That returns permission denied.
Thanks for your time but now i will leave it as its starting to be an issue.

Many thanks though for your efforts in helping.

 

[My Aswer Is This]

That returns permission denied.
Thanks for your time but now i will leave it as its starting to be an issue.

Many thanks though for your efforts in helping.

I test all my scripts and this worked for me.

Take care.
Maybe you have your sheet protected.