ComboBox clear question

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,683
Office Version
  1. 2007
Platform
  1. Windows
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.

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

Have a nice day
 
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.
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

 
Last edited:
Upvote 0
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.
 
Upvote 0
Do you want this named Range loaded into the combobox when the Workbook is opened?

What is the name of the named range?
 
Upvote 0
If you do not want that named range entered on workbook open
Then try this

On workbook open use this:
Combobox1.value=""
 
Upvote 0
I could try that i suppose if you dont mind.

Its called SalesReturns
 
Upvote 0
Try this:
Code:
Private Sub Workbook_Open()
With ThisWorkbook.Sheets("DATABASE").ComboBox1
.Value = ""
.List = Range("SalesReturns").Value
End With
End Sub
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top