user form drop down box

I am creating a user-form for data entry into a particular worksheet.

For one of the fields I'd like to have a drop down box (which I can figure
out how to get there) with two choices to pick from (which I can't figure
out how to get the choices to show up).

Can someone please tell me how to get the choices to show up in the drop
down box of a user form?

Thank you,
Glenn

Hi Glenn

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "Beer"
.AddItem "Cerveza"
.ListIndex = 1
End With
End Sub

HTH. Best wishes Harald

"Glenn" <nospam@yahoo.com> skrev i melding
news:e2v9PnbdFHA.3328@TK2MSFTNGP09.phx.gbl...
> I am creating a user-form for data entry into a particular worksheet.
>
> For one of the fields I'd like to have a drop down box (which I can figure
> out how to get there) with two choices to pick from (which I can't figure
> out how to get the choices to show up).
>
> Can someone please tell me how to get the choices to show up in the drop
> down box of a user form?
>
> Thank you,
> Glenn
>
>

Thank you for asking that question for it gives me the chance to give a
little back to the NG after receiving a whole lot of help from it. I
just went through what you are trying to do this morning.

Here is what I did but there may be a better way.


Private Sub UserForm_Initialize()

YourComboBoxName.AddItem "Your First Item"
YourComboBoxName.AddItem "Your Second Item"

' and so forth

End Sub

Hope this helps.



"Glenn" <nospam@yahoo.com> wrote in message
news:e2v9PnbdFHA.3328@TK2MSFTNGP09.phx.gbl...
>I am creating a user-form for data entry into a particular worksheet.
>
> For one of the fields I'd like to have a drop down box (which I can
> figure
> out how to get there) with two choices to pick from (which I can't
> figure
> out how to get the choices to show up).
>
> Can someone please tell me how to get the choices to show up in the
> drop
> down box of a user form?
>
> Thank you,
> Glenn
>
>

Thanks to both of you. This worked. I am curious about something though -
in the event of a long list or one that is always changing, I can see where
it would be easier to have the data for the drop down box stored on a
worksheet somewhere.

Is that possible?

"Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
news:11be0voc6kive2@corp.supernews.com...
> Thank you for asking that question for it gives me the chance to give a
> little back to the NG after receiving a whole lot of help from it. I
> just went through what you are trying to do this morning.
>
> Here is what I did but there may be a better way.
>
>
> Private Sub UserForm_Initialize()
>
> YourComboBoxName.AddItem "Your First Item"
> YourComboBoxName.AddItem "Your Second Item"
>
> ' and so forth
>
> End Sub
>
> Hope this helps.
>
>
>
> "Glenn" <nospam@yahoo.com> wrote in message
> news:e2v9PnbdFHA.3328@TK2MSFTNGP09.phx.gbl...
>>I am creating a user-form for data entry into a particular worksheet.
>>
>> For one of the fields I'd like to have a drop down box (which I can
>> figure
>> out how to get there) with two choices to pick from (which I can't figure
>> out how to get the choices to show up).
>>
>> Can someone please tell me how to get the choices to show up in the drop
>> down box of a user form?
>>
>> Thank you,
>> Glenn
>>
>>
>
>

Good thinking. There are many ways to do that. Here is three common
solutions; an array, the rowsource property and a loop:

Private Sub UserForm_Initialize()
ComboBox1.List = Sheets(1).Range("A1:A200").Value
End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "=Sheet1!A1:A200"
End Sub

Private Sub UserForm_Initialize()
Dim R As Long
For R = 1 To 200
ComboBox1.AddItem Sheets(1).Cells(R, 1).Text
Next
End Sub

HTH. Best wishes Harald

"Glenn" <nospam@yahoo.com> skrev i melding
news:uMzzopgdFHA.3048@TK2MSFTNGP12.phx.gbl...
> Thanks to both of you. This worked. I am curious about something
though -
> in the event of a long list or one that is always changing, I can see
where
> it would be easier to have the data for the drop down box stored on a
> worksheet somewhere.
>
> Is that possible?
>
> "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
> news:11be0voc6kive2@corp.supernews.com...
> > Thank you for asking that question for it gives me the chance to give a
> > little back to the NG after receiving a whole lot of help from it. I
> > just went through what you are trying to do this morning.
> >
> > Here is what I did but there may be a better way.
> >
> >
> > Private Sub UserForm_Initialize()
> >
> > YourComboBoxName.AddItem "Your First Item"
> > YourComboBoxName.AddItem "Your Second Item"
> >
> > ' and so forth
> >
> > End Sub
> >
> > Hope this helps.
> >
> >
> >
> > "Glenn" <nospam@yahoo.com> wrote in message
> > news:e2v9PnbdFHA.3328@TK2MSFTNGP09.phx.gbl...
> >>I am creating a user-form for data entry into a particular worksheet.
> >>
> >> For one of the fields I'd like to have a drop down box (which I can
> >> figure
> >> out how to get there) with two choices to pick from (which I can't
figure
> >> out how to get the choices to show up).
> >>
> >> Can someone please tell me how to get the choices to show up in the
drop
> >> down box of a user form?
> >>
> >> Thank you,
> >> Glenn
> >>
> >>
> >
> >
>
>

Thanks! Now for my next question -

Say I have two (or more combo boxes). Is there a way to make the choices
that pop up in the second one dependent upon what was picked in the first
one? (For instance if the first one has two choices - "men" and "women",
and if you pick "men" only the names of men will come up in combo box 2?

Thanks again for your help. I really appreciate it.
"Harald Staff" <innocent@enron.invalid> wrote in message
news:%23oX9e$idFHA.2664@TK2MSFTNGP15.phx.gbl...
> Good thinking. There are many ways to do that. Here is three common
> solutions; an array, the rowsource property and a loop:
>
> Private Sub UserForm_Initialize()
> ComboBox1.List = Sheets(1).Range("A1:A200").Value
> End Sub
>
> Private Sub UserForm_Initialize()
> ComboBox1.RowSource = "=Sheet1!A1:A200"
> End Sub
>
> Private Sub UserForm_Initialize()
> Dim R As Long
> For R = 1 To 200
> ComboBox1.AddItem Sheets(1).Cells(R, 1).Text
> Next
> End Sub
>
> HTH. Best wishes Harald
>
> "Glenn" <nospam@yahoo.com> skrev i melding
> news:uMzzopgdFHA.3048@TK2MSFTNGP12.phx.gbl...
> > Thanks to both of you. This worked. I am curious about something
> though -
> > in the event of a long list or one that is always changing, I can see
> where
> > it would be easier to have the data for the drop down box stored on a
> > worksheet somewhere.
> >
> > Is that possible?
> >
> > "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message
> > news:11be0voc6kive2@corp.supernews.com...
> > > Thank you for asking that question for it gives me the chance to give
a
> > > little back to the NG after receiving a whole lot of help from it. I
> > > just went through what you are trying to do this morning.
> > >
> > > Here is what I did but there may be a better way.
> > >
> > >
> > > Private Sub UserForm_Initialize()
> > >
> > > YourComboBoxName.AddItem "Your First Item"
> > > YourComboBoxName.AddItem "Your Second Item"
> > >
> > > ' and so forth
> > >
> > > End Sub
> > >
> > > Hope this helps.
> > >
> > >
> > >
> > > "Glenn" <nospam@yahoo.com> wrote in message
> > > news:e2v9PnbdFHA.3328@TK2MSFTNGP09.phx.gbl...
> > >>I am creating a user-form for data entry into a particular worksheet.
> > >>
> > >> For one of the fields I'd like to have a drop down box (which I can
> > >> figure
> > >> out how to get there) with two choices to pick from (which I can't
> figure
> > >> out how to get the choices to show up).
> > >>
> > >> Can someone please tell me how to get the choices to show up in the
> drop
> > >> down box of a user form?
> > >>
> > >> Thank you,
> > >> Glenn
> > >>
> > >>
> > >
> > >
> >
> >
>
>

Sure, several ways. You should be able to solve this by now ;-)

HTH. best wishes Harald

"Glenn" <me@me.com> skrev i melding
news:OpiSAVldFHA.4040@TK2MSFTNGP14.phx.gbl...
> Thanks! Now for my next question -
>
> Say I have two (or more combo boxes). Is there a way to make the choices
> that pop up in the second one dependent upon what was picked in the first
> one? (For instance if the first one has two choices - "men" and "women",
> and if you pick "men" only the names of men will come up in combo box 2?

Well, I can figure out one way...to make up a user form that has the first
drop down box in it along with a command button that will load a different
user form dependent on what was chosen in the first drop down box.

But, I'd really like this to be all on the same form, to make the process go
quicker.


"Harald Staff" <innocent@enron.invalid> wrote in message
news:u5CCY8ldFHA.1384@TK2MSFTNGP09.phx.gbl...
> Sure, several ways. You should be able to solve this by now ;-)
>
> HTH. best wishes Harald
>
> "Glenn" <me@me.com> skrev i melding
> news:OpiSAVldFHA.4040@TK2MSFTNGP14.phx.gbl...
>> Thanks! Now for my next question -
>>
>> Say I have two (or more combo boxes). Is there a way to make the choices
>> that pop up in the second one dependent upon what was picked in the first
>> one? (For instance if the first one has two choices - "men" and "women",
>> and if you pick "men" only the names of men will come up in combo box 2?
>
>

This solution uses names in Sheet1 A column, and M or F in B column
indicating male / female.

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "Male"
.AddItem "Female"
.ListIndex = 1
End With
End Sub

Private Sub ComboBox1_Click()
Dim R As Long
ComboBox2.Clear
Select Case ComboBox1.ListIndex
Case 0
For R = 1 To 200
If Sheets(1).Cells(R, 2).Value = "M" Then _
ComboBox2.AddItem Sheets(1).Cells(R, 1).Value
Next
Case 1
For R = 1 To 200
If Sheets(1).Cells(R, 2).Value = "F" Then _
ComboBox2.AddItem Sheets(1).Cells(R, 1).Value
Next
Case Else
End Select
End Sub

HTH. Best wishes Harald

"Glenn" <nospam@yahoo.com> skrev i melding
news:un07LNqdFHA.2736@TK2MSFTNGP12.phx.gbl...
> Well, I can figure out one way...to make up a user form that has the first
> drop down box in it along with a command button that will load a different
> user form dependent on what was chosen in the first drop down box.
>
> But, I'd really like this to be all on the same form, to make the process
go
> quicker.
>
>
> "Harald Staff" <innocent@enron.invalid> wrote in message
> news:u5CCY8ldFHA.1384@TK2MSFTNGP09.phx.gbl...
> > Sure, several ways. You should be able to solve this by now ;-)
> >
> > HTH. best wishes Harald
> >
> > "Glenn" <me@me.com> skrev i melding
> > news:OpiSAVldFHA.4040@TK2MSFTNGP14.phx.gbl...
> >> Thanks! Now for my next question -
> >>
> >> Say I have two (or more combo boxes). Is there a way to make the
choices
> >> that pop up in the second one dependent upon what was picked in the
first
> >> one? (For instance if the first one has two choices - "men" and
"women",
> >> and if you pick "men" only the names of men will come up in combo box
2?
> >
> >
>
>