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" wrote in message
...
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" wrote in message
...
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" skrev i melding
...
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" skrev i melding
...
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" wrote in message
...
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" skrev i melding
...
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" skrev i melding
...
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" wrote in message
...
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" wrote in message
...
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" skrev i melding
...
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 wrote in message
...
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" skrev i melding
...
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