Unique Combo and Pass Value

[techissue2008]

Hi

This thread was I posted:
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=1602161

Private Sub Worksheet_Activate()
Sheets("Sheet1").Combo1.Clear
 On Error Resume Next
    Dim s As New Collection, arr, rng As Range
    Dim a As Integer
    Dim i As Integer
    a = Sheets("Sheet2").[K65536].End(xlUp).row
    For Each rng In Sheets("Sheet2").Range("K1:K" & K)
        If rng <> "" Then s.Add rng, CStr(rng)
    Next
    ReDim arr(1 To s.Count)
    For i = 1 To s.Count
        arr(i) = s(i)
    Next
    Combo1.List = arr
    Combo1.ListIndex = 0
End Sub

Why the combo cannot show the value and it is empty?

Any advice?

 

[Norie]

A few bits of advice.

1 Remove the On Error Resume Next.

2 Explain what you actually want to do, I've seen your other posts and it's not clear.

3 And finally, continue in the original thread where people have already been trying to help.

 

[techissue2008]

1) Data sourced from sheet 2, column K, has duplicated and blank values

2) Create a combo drop down menu in sheet 1, non-duplicated and no blank values.

3) Pass selected value to another Sub in module when user chooses from drop down menu.

4) Since I am not sure whether it is data validation, that's why i started a new thread.

 

[techissue2008]

anyone could help?

 

[techissue2008]

anyone could help?

 

[jindon]

Private Sub Worksheet_Activate()
    Dim rng As Range
    Dim K As Long
    Me.Combo1.Clear
    K = Sheets("Sheet2").[K65536].End(xlUp).row
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each rng In Sheets("Sheet2").Range("K1:K" & K)
            If (rng <> "") * (Not .exists(rng.Value) Then .add rng.Value, Nothing
        Next
        If .count > 0 Then
            Me.Combo1.List = .keys
            Me.Combo1.ListIndex = 0
        Ene If
    End With
End Sub

 

[techissue2008]

Hi Jindon

Thanks for your help. But, I encountered a very basic problem. The code cannot find .Combo1.

I created a combo in sheet1, right click it but did not see any properties to set it.

There is a text box to show cell location in top left tool bar. I just set the combo name (Combo1) there.

But, seems it cannot do that. I am using Excel 2002.

 

[jindon]

Hi Jindon

Thanks for your help. But, I encountered a very basic problem. The code cannot find .Combo1.

I created a combo in sheet1, right click it but did not see any properties to set it.

There is a text box to show cell location in top left tool bar. I just set the combo name (Combo1) there.

But, seems it cannot do that. I am using Excel 2002.

You should find out the true name of the combobox.
I can not help you for that.

 

[techissue2008]

I just read http://office.microsoft.com/en-us/excel/HP102366811033.aspx and setup the combo correctly now.

It can show the (K) column value without duplication.

However, how can I pass the selected value to another Sub?

Q.1
.Add rng.Value - does it a string or long type or not both? so that I can set the right parameter of Sub to get it.

Q.2
Me.ComboBox1.List = .keys
Me.ComboBox1.ListIndex = 0

It shows the drop down list, but how can I refer to the (K) column value and pass to another Sub?

Thanks for help

 

[jindon]

However, how can I pass the selected value to another Sub?

Q.1
.Add rng.Value - does it a string or long type or not both? so that I can set the right parameter of Sub to get it.

Q.2
Me.ComboBox1.List = .keys
Me.ComboBox1.ListIndex = 0

It shows the drop down list, but how can I refer to the (K) column value and pass to another Sub?

Thanks for help

1) You don't seem to understand the meaning of that line...
What are you really trying to do ?

2) I don't understand what you are asking.