Syntax To Update ComboBox List From TextBox

[Minitman]

Greetings,

I am trying to update an item in the ComboBox1 list and get this error
message:
-------------------------------------------------------------------------
Run-time error '70':
Could not set the List property. Permission denied.
-------------------------------------------------------------------------

With this code:
---------------------------------------------------------------------------------------
ComboBox1.List(ComboBox1.ListIndex, 2) = TextBox1.Value
---------------------------------------------------------------------------------------

When I run this MsgBox
---------------------------------------------------------------------------------------
MsgBox "TextBox1 = " & TextBox1 & " and ComboBox1 = " _
& ComboBox1.List(ComboBox1.ListIndex, 2)
---------------------------------------------------------------------------------------

Just above the line of code that chokes, it shows that the ComboBox
and the TextBox have the same value.

What do I need to do to fix this code?

Any help is appreciated.

-Minitman

 

[JLGWhiz]

Is the file protected? That is what error 70 implies.

 

[Dave Peterson]

My bet is that you assigned something to the rowsource first--either manually
(via the properties window) or via code.

me.combobox1.rowsource = ""

(or clean up your code/properties setting.)

And you did set the .columncount greater than 1, right?

 

[Minitman]

Hey JLGWhiz,

Thanks for the reply.

Interesting, but this file is not protected.

-Minitman

 

[Minitman]

Hey Dave,

Thanks for the reply.

The row source was set at Initialization with a static named range
(106 columns and 27 rows). When this named range gets changed, I need
to change the row source to reflect the changes.

If I understand your observation, it sounds like I need to first flush
the ComboBox and then reinsert the (changed) named range into the (now
empty) ComboBox row source. And yes, columns are set to ColumnCount.

That is what I was missing.

Thank you.

-Minitman

 

[Dave Peterson]

I guess you could add stuff to the combobox (don't use the rowsource), then you
can continue to add/change stuff via the code you pasted (via the textbox).

Or you could update the worksheet directly and continue using the .rowsource
stuff.

(I'm not sure which way is better for you.)

 

[Minitman]

I'm not sure either.

How would I add stuff to the ComboBox?

The sheet is updated when I leave the UserForm.

I am open to any suggestions you are willing to share.

Again, thanks for the assistance.

-Minitman

 

[Dave Peterson]

You could use .additem and loop through the rows. But this won't work if you
really have to have 106 columns.

I guess you could add stuff to the worksheet, then update the combobox's
rowsource to point to the new range.

 

[Minitman]

How about something like this:

Set rListInput = _
ws1_2.Range("rInput" & iTD) '<<The named range
With I_3
.RowSource = "" "<<The added flusher
.RowSource = "'Input'!" & rListInput.Address
.ColumnCount = rListInput.Columns.Count
.ListIndex = 0
End With

I can't seem to get it to work except at Initialization. I'm not sure
where to call it from.

Any suggestions?

-Minitman

 

[Dave Peterson]

How about right after you update the worksheet.

In your original post, you had this line:
ComboBox1.List(ComboBox1.ListIndex, 2) = TextBox1.Value

I'm not sure where you used this code, but maybe you could:

update the range with the textbox1.value
Then either use similar code to what you used in the userform_initialize
event--or even call that procedure.

ps.
..RowSource = "'Input'!" & rListInput.Address
could be:
..RowSource = rListInput.Address(external:=true)

Then you don't have to worry about the worksheet name changing.

 

[Minitman]

Hey Dave,

Thanks for the PS, that is good to know.

As for the original post, after going over and over my code for the
last week, I realized that the TextBox has nothing to do with what I
am trying to do. This question is about the two ComboBoxes.

The "ComboBox1.List(ComboBox1.ListIndex, 2)" is the value from
ComboBox1 that I need to find a match in the first column of ComboBox2
(another multi-row and multi-column filled ComboBox).

How do I find that match!!!

Any help would be greatly appreciated.

-Minitman

 

[Dave Peterson]

Can you point at the range that is used for the .rowsource for combobox2?

dim res as variant
dim myRng as range
dim myVal as string
with worksheets("Somesheetnamehere")
set myrng = .range("a1:x99") 'or something
end with

with me.combobox1
myval = .list(.listindex,2)
end with
res = application.match(myval, myrng.columns(2), 0)
if iserror(Res) then
'no match
else
'match on row # res of the range (A1:X99)
end if

If all else fails, you could just loop through the entries and compare the
values.

 

[Minitman]

Hey Dave.

Thanks for the reply.

The range for the omboBox2 is:

lLastRow = ws2_1.Range("A65536").End(xlUp).Row
Set rCRefNameList = ws2_1.Range("A1:BT" & lLastRow)

The "rCRefNameList " seems to be the equivalent to "myRng".

I have a question about:
"res = application.match(myval, myrng.columns(2), 0)".

What is the 2? Is it the 2nd or 3rd column that is being checked?

The reason for the confusion is that the item to match is in column 3
of ComboBox1 (.List(.ListIndex)) and the item to be found is in column
1 of ComboBox2.

And I still am not sure how to get the matched row number to reset the
ListIndex number of ComboBox2 or whatever it takes to get the two
ComboBoxes to show the same name.

I appreciate you taking the time to help me with this problem, thanks.

-Minitman

 

[Dave Peterson]

Yes. The (2) is the 2nd column in that range. Change it to what you need.

And maybe you could use:
me.combobox2.value = me.combobox1.value

or

if iserror(res) then
'no match
else
me.combobox2.listindex = res - 1 'starts at 0
end if

 

[Minitman]

That did the trick!

Thanks Dave, I really appreciate the effort you put into this solution

It took me awhile to figure out where to put it, but after much
experimenting, I found the place where it works perfectly. It would
be nice if I knew what I was doing, things would go so much faster!

Again, thanks for helping.

-Minitman

 

[Dave Peterson]

Glad you got it working!

And think of the time you'll save on your next project because of all the things
you learned on this one!

 

[Minitman]

Very true, indeed!!!!!

Glad you got it working!

And think of the time you'll save on your next project because of all the things
you learned on this one!