How to select item using combo-box in ms-excel?

Question

I'm trying to populate a combobox (in excel using vba) which ad items from a range.
It's doing that.
But when I click an item from the dropdown list, it's not showing any item as it's text. (it's text is blank)
If i ad a code to display a msgbox (with anything) in the click event, it's showing the selected item in the combobox!!
The code I used are below:
---
Private Sub ComboBox1_DropButtonClick()
ComboBox1.Clear

For i = 1 To 5
ComboBox1.AddItem Worksheets("Sheet1").Range("a" & i )
Next
End Sub

Private Sub ComboBox1_Click()
'MsgBox “anything”
End Sub
----

The msgbox is not required and I can find no reason of this behavior.
can any one pls help me..

Answer 1

No need to reList your item into combobox every DropDownButton (if the combobox) is clicked

You may construct a list one time only



You can use workshet_activate EVENT,



Private Sub Worksheet_Activate()

Dim i As Integer

ComboBox1.Clear

For i = 1 To 5

ComboBox1.AddItem Worksheets("Sheet1").Range("a" & i)

Next

End Sub



or

Workbook_Open Event



or

Worksheet_Change Event

in this event you have to limit the Target only column A



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

If Target.Column = 1 Then

ComboBox1.Clear

For i = 1 To 5

ComboBox1.AddItem Worksheets("Sheet1").Range("a" & i)

Next

End If

End Sub

Answer 2

You need to add '.Value' to your '.Range("a" & i)' statement.



This works:



Private Sub ComboBox1_DropButtonClick()

ComboBox1.Clear

For i = 1 To 5

ComboBox1.AddItem Worksheets("Sheet1").Range("a" & i).Value

Next

End Sub

Answer 3

I even have merely tried enlarging text cloth in an inserted remark in Excel, possibly it would additionally artwork with a combination field, I clicked on the remark, chosen EDIT, highlighted the text cloth and greater beneficial the text cloth length a pair of notches, yet i think you have already tried something that straightforward.

Answer 4

I surely have basically tried enlarging text textile in an inserted remark in Excel, possibly it may additionally paintings with a combination container, I clicked on the remark, chosen EDIT, highlighted the text textile and greater effective the text textile length a pair of notches, yet i assume you have already tried something that straightforward.