Add Item To ComboBox List

[Minitman]

Greetings,

I have a ComboBox that is loaded when the UserForm is Initialized.
But sometimes I need to add a name to the bottom of the ComboBox list
from a TextBox. I am getting this error message:

Runtime error '70':
Could not set the List property. Permission denied.

Here is the code:

ComboBox1.List(ComboBox1.ListIndex, 2) = TextBox1.Value

I need the value of TextBox1 inserted into the 3rd column of the
ComboBox1 row that is currently chosen.

Is this possible? If so, how do I do it?

Any help would be appreciated.

-Minitman

 

[Minitman]

Update:

I changed the style property back to "0 - fmStyleDropDownCombo". The
code went a little further before it was stopped by this error
message:

Run-time error '381':
Could not get the List property. Invalid property array index.

With this code:

ComboBox1.List(ComboBox1.ListIndex, 2)

I forgot to mention earlier, the named range that is the 'RowSource'
for ComboBox1 is 27 rows high and 105 columns wide. It is the 3rd
column that is displayed in ComboBox1. It is this same third column
(in the 1st empty row) that I am trying to add to from TextBox1.

Anyone have any ideas as to how to eliminate this error?

Any help would be appreciated

-Minitman

 

[Dave Peterson]

If you're using .rowsource, then you won't be able to mess with the list.

You could use .additem, but then you're limited to 10 columns in your combobox.

Do you need all 105 columns showing up in your combobox?

If you do, then you may want to just plop the values of the textbox into the
rowsource range???

 

[Minitman]

Hey Dave,

Thanks for the reply.

Here is the code I use to load ComboBox1 at the UserForm
Initialization:

Set rListInput = ws1_2.Range("rInput" & iTD)
With ComboBox1
.RowSource = "'Input'!" & rListInput.Address
.ColumnCount = rListInput.Columns.Count
.ListIndex = 0
End With

rInput is a series of named ranges. iTD has a range of 1 to 31.
These ranges are 27 rows high and 105 columns wide.

I chose this method for ease of loading and modifying 105 TextBoxes on
a UserForm (code available upon request).

Do you know of a better way to load ComboBox1 and make it dynamic to
change as needed?

-Minitman

 

[Dave Peterson]

Not if you need all 105 columns.

Hey Dave,

Thanks for the reply.

Here is the code I use to load ComboBox1 at the UserForm
Initialization:

Set rListInput = ws1_2.Range("rInput" & iTD)
With ComboBox1
.RowSource = "'Input'!" & rListInput.Address
.ColumnCount = rListInput.Columns.Count
.ListIndex = 0
End With

rInput is a series of named ranges. iTD has a range of 1 to 31.
These ranges are 27 rows high and 105 columns wide.

I chose this method for ease of loading and modifying 105 TextBoxes on
a UserForm (code available upon request).

Do you know of a better way to load ComboBox1 and make it dynamic to
change as needed?

-Minitman

 

[Minitman]

Hey Steve,

Thanks for the reply.

I tried your code. It works somewhat but It has two problems.

It won't continue (it freezes) and it only affects the 1st named range
regardless of what day the user is working with.

I was able adjust the position of the entry, accept for which day it
showed up in (it only shoes up in day 1)..

The name of the chosen named range is Range("rInput" & iTD)

Any ideas as to how to get this to work?

Again thanks for the help.

-Minitman

 

[Minitman]

Hey Dave,

I was afraid of that.

Thanks for the input.

-Minitman

 

[Minitman]

Hey Steve,

Thanks for reply.

Yes, I need the all 105 columns.

As for looping, only to populate the 105 TextBoxes.

As for confusing to look at - I only look at the 3rd column by
adjusting the ComboBox1 ColumnWidths property = "0 pt;0 pt;474 pt".
474 is also the width of ComboBox1.

I finally found a work around by reloading ComboBox1 after putting the
name only into the proper cell and then sending the focus somewhere
else (the work around) and then bring it back to the original page.

If you still want to see the code, let me know. I am willing to share
it, there are quite a few subs involved.

It was while trying to explain what I was trying to do, that the
solution hit me.

Again, thanks

-Minitman