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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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. 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

[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


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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]

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)

"magix" <[email protected]> wrote in message news:[email protected]...
> 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
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[magix]

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[magix]

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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]

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)

"magix" <[email protected]> wrote in message news:[email protected]...
> So usually, what is the common coding in change event ?
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>