|
Syntax To Update ComboBox List From TextBox
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 |
Syntax To Update ComboBox List From TextBox
Is the file protected? That is what error 70 implies.
"Minitman" wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
Hey JLGWhiz,
Thanks for the reply. Interesting, but this file is not protected. -Minitman On Mon, 10 Nov 2008 08:21:02 -0800, JLGWhiz wrote: Is the file protected? That is what error 70 implies. "Minitman" wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
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 On Fri, 14 Nov 2008 10:28:54 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Fri, 14 Nov 2008 10:28:54 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
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 On Fri, 14 Nov 2008 13:30:33 -0600, Dave Peterson wrote: 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 wrote: 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 On Fri, 14 Nov 2008 10:28:54 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
Syntax To Update ComboBox List From TextBox
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 wrote: 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 On Fri, 14 Nov 2008 13:30:33 -0600, Dave Peterson wrote: 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 wrote: 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 On Fri, 14 Nov 2008 10:28:54 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
Syntax To Update ComboBox List From TextBox
Very true, indeed!!!!!
On Wed, 19 Nov 2008 06:52:16 -0600, Dave Peterson wrote: 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 wrote: 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 On Fri, 14 Nov 2008 13:30:33 -0600, Dave Peterson wrote: 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 wrote: 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 On Fri, 14 Nov 2008 10:28:54 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 20:08:49 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 18:38:39 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 15:16:50 -0600, Dave Peterson wrote: 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 wrote: 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 On Mon, 10 Nov 2008 10:32:09 -0600, Dave Peterson wrote: 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 wrote: 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 |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com