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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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

Excel combo box - value list?
 

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