Excel VBA Macro not working

[mcl48236]

Hi All, I am not the best at VBA but am trying to get my macro to work. I have created a combobox drop down list in the developer tab. I want to be able to get the data selected in the combo box into another cell in my workbook, however I am having issues. This is what I have come up with so far

Option Explicit


Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)

On Error Resume Next
With Worksheets("Sheet1")
Lrow = .Range("A" & Rows.Count).End(xlUp).Row
temp = .Range("A2:A" & Lrow).Value
End With

For Each Value In temp
If Len(Value) > 0 Then test.Add Value, CStr(Value)
Next Value

ReDim temp(0)
Worksheets("Sheet1").Shapes("Drop Down 5").ControlFormat.RemoveAllItems

For Each Value In test
Worksheets("Sheet1").Shapes("Drop Down 5").ControlFormat.AddItem Value
Next Value

Set test = Nothing


End Sub


Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 5").ControlFormat
Worksheets("Sheet1").Range("C35") = .List(.Value)
End With
End Sub


but when I go run the macro the cell only populates when I got back into VBA and continue running the selected value part of the code.

Any insights into this would be greatly appreciated!

 

[Badeea]

Thank you Norie and Michael for highlighting the issue of the asterisks and thank you Peter for your explanation. Everyday, we learn something.

 

[mcl48236]

Thank you Norie and Michael for highlighting the issue of the asterisks and thank you Peter for your explanation. Everyday, we learn something.

Sorry for the multiple back and forth I am a serious new-bee to VBA. Now I keep getting an error that the variable is not defined in red below

Option Explicit

Sub ComboBox5_DropButt*******()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)




On Error Resume Next
With Worksheets("Sheet1")
Lrow = .Range("A" & Rows.Count).End(xlUp).Row
temp = .Range("A2:A" & Lrow).Value
End With




For Each Value In temp
If Len(Value) > 0 Then test.Add Value, CStr(Value)
Next Value




ComboBox5.Clear
For Each Value In test
ComboBox5.AddItem Value
Next Value




Set test = Nothing




End Sub




Sub ComboBox5_Click()
Worksheets("Sheet1").Range("C35") = ComboBox5.Text
End Sub

 

[Badeea]

Change the

Sub ComboBox5_Click()

to

Sub ComboBox5_D r o p B u t t o n C l i c k ()

Delete spaces between letters of the above blue word. It should work.

 

[mcl48236]

Change the

Sub ComboBox5_Click()

to

Sub ComboBox5_D r o p B u t t o n C l i c k ()

Delete spaces between letters of the above blue word. It should work.

I did that and now I'm get an error that says ambiguous name detected

 

[Norie]

If we stick with the original code from post #1 have you assigned any macro to the combobox?

Also, how are you running the code to populate the combobox?