Excel combo box - value list?

I created a form through VBA in Excel, and added a combo box to it. Much to
my dismay, and unlike MS Access, it seems like the only way to populate the
rowsource property is to bind it to a column on a spreadsheet.

In MS Access, you can select rowsourcetype = value list, and then hand-type
the values you'd like to show up in the combo box drop-down in the rowsource
property.

I really don't want to have to add another whole worksheet to my workbook to
accomodate a handful of options for a combo box. plus, I seem to be running
into issues if the worksheet with the applicable values is not the active
worksheet when the form is displayed. being able to hand-type the values like
you can in MS Access would be so much nicer.

Is there a way to do this in Excel? Am I missing something?

Thanks, Joel

You can either use add item to assign the values in code or you can link
(bind) to the worksheet cells using rowsource.

You can do

userform1.Combobox1.List = Array(1,"Bob",3,4,5,6,"Sally")

Such code could be placed in the initialize event.

To the best of my knowledge, there is no provision to type in values.

--
Regards,
Tom Ogilvy


"Joel" <Joel@discussions.microsoft.com> wrote in message
news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> I created a form through VBA in Excel, and added a combo box to it. Much
to
> my dismay, and unlike MS Access, it seems like the only way to populate
the
> rowsource property is to bind it to a column on a spreadsheet.
>
> In MS Access, you can select rowsourcetype = value list, and then
hand-type
> the values you'd like to show up in the combo box drop-down in the
rowsource
> property.
>
> I really don't want to have to add another whole worksheet to my workbook
to
> accomodate a handful of options for a combo box. plus, I seem to be
running
> into issues if the worksheet with the applicable values is not the active
> worksheet when the form is displayed. being able to hand-type the values
like
> you can in MS Access would be so much nicer.
>
> Is there a way to do this in Excel? Am I missing something?
>
> Thanks, Joel

Hi Joel

You don't have to data bind it. If you have few values (<20 or so) then code
is easier:

Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "Beer"
.AddItem "Wine"
.AddItem "Bourbon"
End With
End Sub

With longer lists I find a bound worksheet easier to maintain.

HTH. Best wishes Harald

"Joel" <Joel@discussions.microsoft.com> skrev i melding
news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> I created a form through VBA in Excel, and added a combo box to it. Much
to
> my dismay, and unlike MS Access, it seems like the only way to populate
the
> rowsource property is to bind it to a column on a spreadsheet.
>
> In MS Access, you can select rowsourcetype = value list, and then
hand-type
> the values you'd like to show up in the combo box drop-down in the
rowsource
> property.
>
> I really don't want to have to add another whole worksheet to my workbook
to
> accomodate a handful of options for a combo box. plus, I seem to be
running
> into issues if the worksheet with the applicable values is not the active
> worksheet when the form is displayed. being able to hand-type the values
like
> you can in MS Access would be so much nicer.
>
> Is there a way to do this in Excel? Am I missing something?
>
> Thanks, Joel

you can populate from a range, or use the .ADD method


"Joel" <Joel@discussions.microsoft.com> wrote in message
news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
>I created a form through VBA in Excel, and added a combo box to it. Much to
> my dismay, and unlike MS Access, it seems like the only way to populate
> the
> rowsource property is to bind it to a column on a spreadsheet.
>
> In MS Access, you can select rowsourcetype = value list, and then
> hand-type
> the values you'd like to show up in the combo box drop-down in the
> rowsource
> property.
>
> I really don't want to have to add another whole worksheet to my workbook
> to
> accomodate a handful of options for a combo box. plus, I seem to be
> running
> into issues if the worksheet with the applicable values is not the active
> worksheet when the form is displayed. being able to hand-type the values
> like
> you can in MS Access would be so much nicer.
>
> Is there a way to do this in Excel? Am I missing something?
>
> Thanks, Joel

Harald,
Thanks, this helps.

And, unless they add more months to the year, this combo box list will
pretty much always contain 12 values. : )

Joel

"Harald Staff" wrote:

> Hi Joel
>
> You don't have to data bind it. If you have few values (<20 or so) then code
> is easier:
>
> Private Sub UserForm_Initialize()
> With Me.ComboBox1
> .AddItem "Beer"
> .AddItem "Wine"
> .AddItem "Bourbon"
> End With
> End Sub
>
> With longer lists I find a bound worksheet easier to maintain.
>
> HTH. Best wishes Harald
>
> "Joel" <Joel@discussions.microsoft.com> skrev i melding
> news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > I created a form through VBA in Excel, and added a combo box to it. Much
> to
> > my dismay, and unlike MS Access, it seems like the only way to populate
> the
> > rowsource property is to bind it to a column on a spreadsheet.
> >
> > In MS Access, you can select rowsourcetype = value list, and then
> hand-type
> > the values you'd like to show up in the combo box drop-down in the
> rowsource
> > property.
> >
> > I really don't want to have to add another whole worksheet to my workbook
> to
> > accomodate a handful of options for a combo box. plus, I seem to be
> running
> > into issues if the worksheet with the applicable values is not the active
> > worksheet when the form is displayed. being able to hand-type the values
> like
> > you can in MS Access would be so much nicer.
> >
> > Is there a way to do this in Excel? Am I missing something?
> >
> > Thanks, Joel
>
>
>

Thanks, this helps.

What's the difference between putting it in the Initialize event vs. the
Activate event?


"Tom Ogilvy" wrote:

> You can either use add item to assign the values in code or you can link
> (bind) to the worksheet cells using rowsource.
>
> You can do
>
> userform1.Combobox1.List = Array(1,"Bob",3,4,5,6,"Sally")
>
> Such code could be placed in the initialize event.
>
> To the best of my knowledge, there is no provision to type in values.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Joel" <Joel@discussions.microsoft.com> wrote in message
> news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > I created a form through VBA in Excel, and added a combo box to it. Much
> to
> > my dismay, and unlike MS Access, it seems like the only way to populate
> the
> > rowsource property is to bind it to a column on a spreadsheet.
> >
> > In MS Access, you can select rowsourcetype = value list, and then
> hand-type
> > the values you'd like to show up in the combo box drop-down in the
> rowsource
> > property.
> >
> > I really don't want to have to add another whole worksheet to my workbook
> to
> > accomodate a handful of options for a combo box. plus, I seem to be
> running
> > into issues if the worksheet with the applicable values is not the active
> > worksheet when the form is displayed. being able to hand-type the values
> like
> > you can in MS Access would be so much nicer.
> >
> > Is there a way to do this in Excel? Am I missing something?
> >
> > Thanks, Joel
>
>
>

Months ? Why didn't you say so ? This is all it takes:

Private Sub UserForm_Initialize()
Dim L As Long
With ComboBox1
For L = 1 To 12
.AddItem Format$(DateSerial(1, L, 1), "mmmm")
Next
End With
End Sub

HTH. Best wishes Harald

"Joel" <Joel@discussions.microsoft.com> skrev i melding
news:273AABCF-021B-4B13-ADA6-B69596F1FA50@microsoft.com...
> Harald,
> Thanks, this helps.
>
> And, unless they add more months to the year, this combo box list
will
> pretty much always contain 12 values. : )
>
> Joel
>
> "Harald Staff" wrote:
>
> > Hi Joel
> >
> > You don't have to data bind it. If you have few values (<20 or so) then
code
> > is easier:
> >
> > Private Sub UserForm_Initialize()
> > With Me.ComboBox1
> > .AddItem "Beer"
> > .AddItem "Wine"
> > .AddItem "Bourbon"
> > End With
> > End Sub
> >
> > With longer lists I find a bound worksheet easier to maintain.
> >
> > HTH. Best wishes Harald
> >
> > "Joel" <Joel@discussions.microsoft.com> skrev i melding
> > news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > > I created a form through VBA in Excel, and added a combo box to it.
Much
> > to
> > > my dismay, and unlike MS Access, it seems like the only way to
populate
> > the
> > > rowsource property is to bind it to a column on a spreadsheet.
> > >
> > > In MS Access, you can select rowsourcetype = value list, and then
> > hand-type
> > > the values you'd like to show up in the combo box drop-down in the
> > rowsource
> > > property.
> > >
> > > I really don't want to have to add another whole worksheet to my
workbook
> > to
> > > accomodate a handful of options for a combo box. plus, I seem to be
> > running
> > > into issues if the worksheet with the applicable values is not the
active
> > > worksheet when the form is displayed. being able to hand-type the
values
> > like
> > > you can in MS Access would be so much nicer.
> > >
> > > Is there a way to do this in Excel? Am I missing something?
> > >
> > > Thanks, Joel
> >
> >
> >

The initialize event is fired once. the activate event could be fired more
than once. Other than that, I would think it is a matter of preference.

--
Regards,
Tom Ogilvy

"Joel" <Joel@discussions.microsoft.com> wrote in message
news:4418B32D-7448-48CF-8234-A14330C6D570@microsoft.com...
> Thanks, this helps.
>
> What's the difference between putting it in the Initialize event vs. the
> Activate event?
>
>
> "Tom Ogilvy" wrote:
>
> > You can either use add item to assign the values in code or you can link
> > (bind) to the worksheet cells using rowsource.
> >
> > You can do
> >
> > userform1.Combobox1.List = Array(1,"Bob",3,4,5,6,"Sally")
> >
> > Such code could be placed in the initialize event.
> >
> > To the best of my knowledge, there is no provision to type in values.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Joel" <Joel@discussions.microsoft.com> wrote in message
> > news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > > I created a form through VBA in Excel, and added a combo box to it.
Much
> > to
> > > my dismay, and unlike MS Access, it seems like the only way to
populate
> > the
> > > rowsource property is to bind it to a column on a spreadsheet.
> > >
> > > In MS Access, you can select rowsourcetype = value list, and then
> > hand-type
> > > the values you'd like to show up in the combo box drop-down in the
> > rowsource
> > > property.
> > >
> > > I really don't want to have to add another whole worksheet to my
workbook
> > to
> > > accomodate a handful of options for a combo box. plus, I seem to be
> > running
> > > into issues if the worksheet with the applicable values is not the
active
> > > worksheet when the form is displayed. being able to hand-type the
values
> > like
> > > you can in MS Access would be so much nicer.
> > >
> > > Is there a way to do this in Excel? Am I missing something?
> > >
> > > Thanks, Joel
> >
> >
> >

I noticed that in vba 6.3, version 9972 the additem method doesn't compile,
but it does in verison 9969. Any body seen that??

"Joel" wrote:

> Harald,
> Thanks, this helps.
>
> And, unless they add more months to the year, this combo box list will
> pretty much always contain 12 values. : )
>
> Joel
>
> "Harald Staff" wrote:
>
> > Hi Joel
> >
> > You don't have to data bind it. If you have few values (<20 or so) then code
> > is easier:
> >
> > Private Sub UserForm_Initialize()
> > With Me.ComboBox1
> > .AddItem "Beer"
> > .AddItem "Wine"
> > .AddItem "Bourbon"
> > End With
> > End Sub
> >
> > With longer lists I find a bound worksheet easier to maintain.
> >
> > HTH. Best wishes Harald
> >
> > "Joel" <Joel@discussions.microsoft.com> skrev i melding
> > news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > > I created a form through VBA in Excel, and added a combo box to it. Much
> > to
> > > my dismay, and unlike MS Access, it seems like the only way to populate
> > the
> > > rowsource property is to bind it to a column on a spreadsheet.
> > >
> > > In MS Access, you can select rowsourcetype = value list, and then
> > hand-type
> > > the values you'd like to show up in the combo box drop-down in the
> > rowsource
> > > property.
> > >
> > > I really don't want to have to add another whole worksheet to my workbook
> > to
> > > accomodate a handful of options for a combo box. plus, I seem to be
> > running
> > > into issues if the worksheet with the applicable values is not the active
> > > worksheet when the form is displayed. being able to hand-type the values
> > like
> > > you can in MS Access would be so much nicer.
> > >
> > > Is there a way to do this in Excel? Am I missing something?
> > >
> > > Thanks, Joel
> >
> >
> >

Possibly you have an invalid reference in the machine where you say it
doesn't compile. After the error, in the VBE hit reset, then go into
tools=>References and see if you have a reference marked as MISSING. If so,
fix it.

--
Regards,
Tom Ogilvy


"chan b" <chan b@discussions.microsoft.com> wrote in message
news:A80DC190-C933-470C-B47D-EAE81282E7F1@microsoft.com...
> I noticed that in vba 6.3, version 9972 the additem method doesn't
compile,
> but it does in verison 9969. Any body seen that??
>
> "Joel" wrote:
>
> > Harald,
> > Thanks, this helps.
> >
> > And, unless they add more months to the year, this combo box list
will
> > pretty much always contain 12 values. : )
> >
> > Joel
> >
> > "Harald Staff" wrote:
> >
> > > Hi Joel
> > >
> > > You don't have to data bind it. If you have few values (<20 or so)
then code
> > > is easier:
> > >
> > > Private Sub UserForm_Initialize()
> > > With Me.ComboBox1
> > > .AddItem "Beer"
> > > .AddItem "Wine"
> > > .AddItem "Bourbon"
> > > End With
> > > End Sub
> > >
> > > With longer lists I find a bound worksheet easier to maintain.
> > >
> > > HTH. Best wishes Harald
> > >
> > > "Joel" <Joel@discussions.microsoft.com> skrev i melding
> > > news:68C1651B-6666-41D0-9177-B87947852C3D@microsoft.com...
> > > > I created a form through VBA in Excel, and added a combo box to it.
Much
> > > to
> > > > my dismay, and unlike MS Access, it seems like the only way to
populate
> > > the
> > > > rowsource property is to bind it to a column on a spreadsheet.
> > > >
> > > > In MS Access, you can select rowsourcetype = value list, and then
> > > hand-type
> > > > the values you'd like to show up in the combo box drop-down in the
> > > rowsource
> > > > property.
> > > >
> > > > I really don't want to have to add another whole worksheet to my
workbook
> > > to
> > > > accomodate a handful of options for a combo box. plus, I seem to be
> > > running
> > > > into issues if the worksheet with the applicable values is not the
active
> > > > worksheet when the form is displayed. being able to hand-type the
values
> > > like
> > > > you can in MS Access would be so much nicer.
> > > >
> > > > Is there a way to do this in Excel? Am I missing something?
> > > >
> > > > Thanks, Joel
> > >
> > >
> > >