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]

Hi mcl48236,

I have the following suggestion to solve your VBA issue.

First, create a ComboBox5 (AxtiveX Controls) instead of DropDown5 (Form Controls).

Then, paste the following code and let me know if this is what you want.

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

 

[mcl48236]

Hi mcl48236,

I have the following suggestion to solve your VBA issue.

First, create a ComboBox5 (AxtiveX Controls) instead of DropDown5 (Form Controls).

Then, paste the following code and let me know if this is what you want.

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

Thank you so much for your response!
I now get a compile error: expect end of statement. Any thoughts?

 

[Badeea]

Can you copy your code and send it to me?

 

[Norie]

Have you assigned a macro to the dropdown or set the Linked Cell for it?

 

[Michael M]

Remove the asterisks from this line

Sub ComboBox5_DropButt*******()

 

[Norie]

Michael

I think it's the board that adds them for some strange reason.

PS Having * in a sub/function name isn't allowed.

 

[Michael M]

Hi Norie
I've never seen the added ******* before !
I was aware of them not being allowed, hence my suggestion.

 

[Peter_SSs]

One of the automatic replacements in the forum is "o n c l i c k" (without the spaces). Presumably the code was
ComboBox5_D r o p B u t t o n c l i c k (without the spaces)

 

[Michael M]

Thanks Peter
I wasn't aware of that, and to be honest, have never seen it before !