ComboBox returns only index number - I want it to return the text

[SashaFromSydney]

Hi all,

newbie here with excel 2010 macro: created a combobox (from under 'form controls'), the purpose of which is that the user makes a selection which then populates the active cell (ie the cell the user highlighted before making the selection from the combobox). The population should happen upon click event.

I have managed to achieve this, however the code (below) populates the active cell with the index number of the selected entry (e.g. if user chooses first entry from combobox, the active cell is populated with a "1"), however I want the actual selection text to populate the active cell.

Help appreciated.

Sub Combobox1_UserClick()
Dim sht As Worksheet
Dim mycell As Range
worksheets("unit decision").Activate
'mycell = Application.worksheets("unit decision").ActiveCell
Set sht = Application.worksheets("unit decision")
ActiveCell.Value = sht.Shapes("ComboBox1").ControlFormat.Value
'If ComboBox1.ListIndex <> -1 Then
'ActiveSheet.ActiveCell.Value = worksheets("Sheet2").ComboBox1.Text
'End If
End Sub

 

[Danerida]

Welcome to the forum. Please use code tags when posting code (it's the # button)
Your code returns the ControlFormat.Value not the combobox text.
Try:

Sub Combobox1_UserClick()Dim sht As Worksheet
Dim mycell As Range
worksheets("unit decision").Activate
'mycell = Application.worksheets("unit decision").ActiveCell
Set sht = Application.worksheets("unit decision")
ActiveCell.Value = sht.Shapes("ComboBox1").Value ' OR sht.Shapes("ComboBox1").text
'If ComboBox1.ListIndex <> -1 Then
'ActiveSheet.ActiveCell.Value = worksheets("Sheet2").ComboBox1.Text
'End If
End Sub

Dan