Excel-Getting selected value to stay in combo box on user form

[psbrown]

Hi I am new to VBA.

I found this code on this forum and have used it to populate my combo box on a user form in Excel, the problem is when a value is selected the value is not retained in the box it just goes blank.

Any pointers on what I am doing wrong would be appreciated.

Thanks

Paul

Private Sub CmbFirstPer_DropButtonClick()
Dim ListRange As Range, c As Range
' change the below to match your data's range
Set ListRange = ThisWorkbook.Sheets("periodhours").Range("A1:iv1")
With CmbFirstPer
.RowSource = "" ' clear the RowSource, if set
.Clear ' clear ListBox
'.AddItem "All" 'Insert "All" item
For Each c In ListRange
.AddItem c.Value ' Insert the values from ListRange
Next c
'.ListIndex = 0 ' Select the first item ("All")
End With
End Sub

 

[rmikesmith]

I think the main problem is you are populating the combobox from within the DropButton_Click event. This event is fired when the user clicks on the dropdown button, exposing the list and also when the list disappears. Any selection made in between is erased.


Regards,
Mike

 

[xlbo]

....so what Mike is trying to say is you should populate the combobox in the Userform_Initialize event

 

[rmikesmith]

Thanks Geoff! You have to admit, I had a good story going right up until I stopped writing it

Mike

 

[xlbo]

well, I thought it was so well written up to that point that it needed finishing off ;-)

 

[psbrown]

That works great, what do they say about a liitle knowledege is dangerous.

Thanks Guys