Combo Box

[magix]

Hi,

In my userform, i have

Firstname: <DropdownList>

Question:
1. If I want the value of DropDownList to be read from code, how can I do it
via code by hardcoding the entries in combobox_change() function ?
Private Sub ComboBox1_Change()
....
End Sub

2. If I want the value of DropDownList to be read from Column A1:A10 (note
there are redundant value, how to avoid ?)
Example:
A1: Johnson
A2: Stephen
A3: Michael
A4: Daryl
A5: Michael
A6: Johnson
A7: Rebecca
A8: Johnson
A9: Sally
A10: Sally

So the drop down list will actually read from A1: A10 and have only
Johnson
Stephen
Michael
Daryl
Rebecca
Sally


3. After I click OK in the userform, how can I pass the selected value to my
module code ?

Please advise. Thanks

Regards,
Magix

 

[Bob Phillips]

1. Me.Combobox1.Value

2. Use a dictionary onject

Dim oDic As Object
Dim i As Long
Dim aItems
Set oDic = CreateObject("Scripting.Dictionary")
On Error Resume Next
For i = 1 To Range("A1").End(xlDown).Row
oDic.Add Cells(i, "A").Value, Cells(i, "A")
End With
On Error GoTo 0

'trabsefr dictionary to an array
aItems = oDic.Items
With Me.combobox1
.Clear
For i = 0 To oDic.Count - 1
.AddItem aItems(i)
Next
End With
Set oDic = Nothing

3. In the OK button click event

Activesheet.Range("A1").Value = me.Combobox1.Value

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[magix]

Hi Bob,

1. Do you mean I put like this ?
Private Sub ComboBox1_Change()
Me.Combobox1.Value = "David"
Me.Combobox1.Value = "Rebecca"
Me.Combobox1.Value = "Kenix"
Me.Combobox1.Value = "Isaac"
End Sub

2. Where should I put this code that you written ? in " Private Sub
ComboBox1_Change( )" ?
Dim oDic As Object
...
...
...
Set oDic = Nothing

Regards.

 

[Bob Phillips]

1. Why would you want multiple values on the change. What I gave you showed
you how to get the selected value. probably in the click event. If you are
trying to load the combobox. you need

Me.Combobox1.AddItem "David"

etc., but not in the change event, in the userform ac tivate event.

2. Again in userform activate when you are getting all the values.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[magix]

Hi Bob,

I tried with your code, and it works.

Dim oDic As Object
Dim i As Long
Dim aItems
Set oDic = CreateObject("Scripting.Dictionary")
On Error Resume Next
For i = 1 To Range("A1").End(xlDown).Row
oDic.Add Cells(i, "A").Value, Cells(i, "A")
Next i
On Error GoTo 0

'trabsefr dictionary to an array
aItems = oDic.Items
With Me.combobox1
.Clear
For i = 0 To oDic.Count - 1
.AddItem aItems(i)
Next
End With
Set oDic = Nothing

How can I modify your code to filter the value that contains certain
substring, which I do not want it to be add into the list ?
Based on my example below: the output should be
Johnson
Stephen
Michael
Daryl
Rebecca
Sally

If I want to add additional filter to filter if the value contains substring
"Reb", it is has "Reb", I will not add it into combo list. So the final list
will be
Johnson
Stephen
Michael
Daryl
Sally

Hope you understand what I mean.

Thank you.

Best Regards,
Magix

 

[magix]

Hi Bob,

If I use your dictionary code, then in Change event, how should I do ? is it
like this ? But I think this is not so appropriate.

let say in my combo box, i have list
David
Johnson
Michael

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "David"
Me.ComboBox1.Value = "David"
Case "Johnson"
Me.ComboBox1.Value = "Johnson"
Case "Michael"
Me.ComboBox1.Value = "Michael"
Case Else
End Select
End Sub

If not, what is the appropriate one, with respect to your dictionary object
?

BestRegards,
Magix

 

[Bob Phillips]

I think this is what you mean

Dim oDic As Object
Dim i As Long
Dim aItems
Set oDic = CreateObject("Scripting.Dictionary")
On Error Resume Next
For i = 1 To Range("A1").End(xlDown).Row
If Not Cells(i,"A").Value Like "*Reb*" Then
oDic.Add Cells(i, "A").Value, Cells(i, "A").Value
End If
End With
On Error GoTo 0

'transfer dictionary to an array
aItems = oDic.Items
With Me.combobox1
.Clear
For i = 0 To oDic.Count - 1
.AddItem aItems(i)
Next
End With
Set oDic = Nothing


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

I really do not understand what you are trying to do here, you test an
object for David, and if true, set it to David!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[magix]

I really do not understand what you are trying to do here, you test an
object for David, and if true, set it to David!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

is

 

[magix]

So usually, what is the common coding in change event ?

 

[Bob Phillips]

You get the changed value, and you process it in some way, which only you
can say what that should be.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)