ComboBox.Value cannot be called in VBA?

[Petr_R]

Hi there,
I am using microsoft Excel 2003 Sp2. I have a problem after changing
Combobox list it remains there visible previous selected value even if it is
not actual anymore. It can be changed only by manually clicking and selecting
some new value. I found that there are 2 parameters in Combobox properties
(Text, Value) that changes this residual value, but when i am trying to
change it thru VB code (line is now commented) i get error 438 - object
doesn't support this method. So how can i change / delete the old value from
VB?

Thank you for help


private Sub UpdateInfo()

Set Data = Worksheets("Data")
Set Info = Worksheets("Info")
Dim cboTemp As OLEObject

Set cboTemp = Info.OLEObjects("SmlouvyC")
j = 0
With cboTemp
.Visible = True
.ListFillRange = ""
.LinkedCell = ""

.Visible = False
For i = 1 To 50

Data.Cells(j + 4, 1).Value = Data.Cells(KC, i + 7).Value
j = j + 1
If Data.Cells(KC, i + 7).Value = "" Then
GoTo LastRecord
End If
Next i
LastRecord:
.ListFillRange = "DATA!" + Range(Cells(4, 1), Cells(j + 2, 1)).Address
'.Value = ""
.Visible = True

End With


End Sub

 

[dbKemp]

Hi there,
I am using microsoft Excel 2003 Sp2. I have a problem after changing
Combobox list it remains there visible previous selected value even if it is
not actual anymore. It can be changed only by manually clicking and selecting
some new value. I found that there are 2 parameters in Combobox properties
(Text, Value) that changes this residual value, but when i am trying to
change it thru VB code (line is now commented) i get error 438 - object
doesn't support this method. So how can i change / delete the old value from
VB?

Thank you for help

private Sub UpdateInfo()

Set Data = Worksheets("Data")
Set Info = Worksheets("Info")
Dim cboTemp As OLEObject

Set cboTemp = Info.OLEObjects("SmlouvyC")
j = 0
With cboTemp
.Visible = True
.ListFillRange = ""
.LinkedCell = ""

.Visible = False
For i = 1 To 50

Data.Cells(j + 4, 1).Value = Data.Cells(KC, i + 7).Value
j = j + 1
If Data.Cells(KC, i + 7).Value = "" Then
GoTo LastRecord
End If
Next i
LastRecord:
.ListFillRange = "DATA!" + Range(Cells(4, 1), Cells(j + 2, 1)).Address
'.Value = ""
.Visible = True

End With

End Sub

Try:
Dim cboTemp As ComboBox
Set cboTemp = Info.OLEObjects("SmlouvyC").Object