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???

 

[Incidental]

Hi Minitman

if you are using Rowsource you will have to pass the data from your
textbox to your worksheet which will in turn update your combobox,
something like the code below will do what you want i think. it will
change the value in the third column of the selected row in the
combobox.

With Sheets("Sheet1")

.Cells(ComboBox1.ListIndex + 1, 3).Value = TextBox1.Value

End With

hope this helps

Steve

 

[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

 

[Incidental]

Hey Minitman

I am a little confused as to what you are trying to actually do? As
posted by Dave do you need all the columns? when i set up a test sheet
with it all that data in a single combobox it was very confusing to
look at. Perhaps you could include some code and some more
information on what you are doing. i.e. are you wanting to loop
through multiple ranges adding data to each of them. Keeping track of
all 105 columns will be very hard on the eyes but that's only my
opinion. You mention 105 textboxes also, would i be right in thinking
you load all your data to the combobox and then make a selection in
that control to display data in the 105 textboxes on the form?? I
don't know i'm just guessing now but i will try and be of help to you
if you can clarify things a little more.

Cheers

Steve

 

[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

 

[Incidental]

Hi Minitman

I'm glad you got it sorted, take it easy

Steve