Combo box issues

[Thecraftycarrot]

Hi All,

I have a searchable combobox that works perfectly.

However i have been having some issues where the search box remains open.

If ComboBox1.Value  = "" Then Exit Sub
ComboBox1.ListFillRange = "nameSearch2"
Me.ComboBox1.DropDown
end sub

The above is the code that i have.

When i activate the macro, it clears this with

sheets("sheet2").range("a2) =""

i also tried the above code with .value at the end, same result.

however once completed the combox list remains open, across multiple tabs and workbooks, the only way yo remove it is clicking on it, which is not what i want. a) because it shouldn't do /require that b) runs the risk of populating the linked cell with values.

any help would be appreciated been pulling my hair out for days now trying to figure this out.

i hope this makes sense.

 

[Thecraftycarrot]

I also forgot to mention that the list also randomly pops up as i am navigating through the file, e.g. changing filteres on a table etc.

I stopped this by changing the above code from = "" to <> "", however the same issues as previously mentioned happens as well as the list is permenantly appearing until a name has been selected.

 

[Yongle]

Is your combo-box a sheet-embedded active-X control?

 

[Thecraftycarrot]

Yes it is

 

[Yongle]

I cannot replicate your problem, but this may work and is quick to test
- we can tidy things up afterwards if the test is successful
- the code will also require triggering when the workbook is deactivated

1. Add an active-x Label to the sheet
2. Right-click on it \ select Order \ select Send to Back
3. Make it 2cm lomger and 2cm taller than the combobox
4. Place it behind the combobox so that the combobox sits in the middle of the label
5. Add code below to the sheet module (amend if Label is not Label1 )
6. Go back to Excel, ensure not in Design Mode, and test

The Label MouseMove event should decativate the combobox when the cursor moves over the label

Let me know how you get on

Private Sub ComboBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    EnableComboBox True
End Sub

Private Sub [COLOR=#ff0000]Label1[/COLOR]_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    EnableComboBox True
    EnableComboBox False
End Sub

Private Sub EnableComboBox(TrueFalse As Boolean)
     With ComboBox1
        If Not .Enabled = TrueFalse Then .Enabled = TrueFalse
    End With
End Sub

You may think it odd to have both True and False within Label MouseMove - I also think that!
- but during my testing, the focus would sometimes not trigger back to the combobox without flicking the switch like that

 

[Thecraftycarrot]

Unfortunatly that has not resolved it, it still appearing in the same place which is weird as its starts at the top of the green ribbon in excel, down into the worksheet. i have a screenshot but cannot seem to figure out how to upload an image here.

 

[Yongle]

A picture would not help

Are you using Windows? Which version of Excel?

Did my code work as expected within the original sheet?
- if not insert a NEW combobox and amend the code to apply to that and test again

Let me know, thanks

 

[Thecraftycarrot]

Doesnt work,

I just dont get it

the issue where it randomly appears when navigating the spreadsheet etc, disappears with the following at the start of the code(1) but that code causes the same issue when there is no information in the combo box which is resolved with 2), but then we are at the original problem where it randomly appears across spreadsheets etc when it has been populated

1) If ComboBox1.Value  <> "" Then Exit Sub

2) If comboBox1.value = "" Then Exit Sub

 

[Yongle]

A simple fix did nothing.
To try to get to the bottom of this, I need to get an understanding of what exactly is going on in detail

1. Are you using Windows?
2. Which version of Excel are you using?
3. I have a searchable combobox that works perfectly - how does it work?
4. ComboBox properties - which ones are NOT set to default?
5. Please post all code relating to the combobox in full