Zathras...lend an ear! Combobox woes part 2

[ctbaker]

Hey,

Since you gave me tons of help before, I am afraid you are getting stuck with another Combobox question.

I have a combobox called ExistIIP1 and when a user inputs their own custom ExistIIP1.Text value, I want the ExistIIP1.Value to equal some text I specify. In this example I am using the word "Krunk".

Private Sub ExistIIP1_Change()
ExistIIP1.Value = "Krunk"
End Sub

This is independent of the two column combobox that ExistIIP1 is defined as. All this explanation I think is really not necessary perhaps as the real problem is that when the below code is executed I get a:

"Could not set the Value property. Invalid property value."

error. But since I don't know squat about all this Visual Basic in Excel junk I am working in it has me puzzled.

I tried using a variation of your helpful code before of:

Dim i As Integer
Dim o As ComboBox
With Sheets("Funding Sheet&quot
For i = 1 To .OLEObjects.Count
If TypeName(Sheets("Funding Sheet&quot.OLEObjects(i).Object) = "ComboBox" Then
Set o = Sheets("Funding Sheet&quot.OLEObjects(i).Object
If o.Name = ItemName Then
o.Value = "Krunk"
If o.LinkedCell <> &quot;&quot; Then .Range(o.LinkedCell) = &quot;&quot;
End If
End If
Next i
End With
Set o = Nothing
End Sub

And when it goes to write the o.value I get the same error.


Anyways, I hope I am explaining this worth a darn.

Thanks for any help you can give!!

 

[Zathras]

Thanks for the vote of confidence. I have not really used combo boxes this way before, but with a little experimentation here is what I came up with.

First though, I would suggest that you not change the name of the combo box after you drop it on the worksheet. Things are complicated enough without doing that. If you just keep it as ComboBox1 you don't have to search for it later.

If I understand what you are trying to do, you have a 2-column combo box that you want to initialize with some data and then allow the user to add items to it by specifying a new item for the first column to which you will figure out what should go in the second column. If that is a correct understanding, then experiment with the following until it does what you need:
[blue]

Option Explicit

Sub test()
  Call InitializeComboBox1
  Call AddComboBox1Entry("YYY", "999")
  Call AddComboBox1Entry("XXX", "888")
End Sub

Sub InitializeComboBox1()
Dim MyArray(5, 1)
  MyArray(0, 0) = "AA":  MyArray(0, 1) = "111"
  MyArray(1, 0) = "BB":  MyArray(1, 1) = "222"
  MyArray(2, 0) = "CC":  MyArray(2, 1) = "333"
  MyArray(3, 0) = "DD":  MyArray(3, 1) = "444"
  MyArray(4, 0) = "EE":  MyArray(4, 1) = "555"
  MyArray(5, 0) = "FF":  MyArray(5, 1) = "666"
  With Sheets("Sheet1").ComboBox1
    .Clear
    .ColumnCount = 2
    .List() = MyArray
  End With
End Sub

Sub AddComboBox1Entry(Col1Entry As String, Col2Entry As String)
  With Sheets("Sheet1").ComboBox1
    .AddItem (Col1Entry)
    .Text = Col1Entry
    .Column(1) = Col2Entry
  End With
End Sub

[/color]
Everything just goes into a code module. Open a new workbook and drop a combo box on &quot;Sheet1&quot;. Enjoy!