Hi,

I currently stuck with a problem in vba with the worksheet_change
event. I generate during the activation of a sheet multiple comboboxes,
but needs to know when the value of a combobox has been changed. First
of all I tried it with the worksheet_change sub, but a change of the
combobox that is linked to a cell does not trigger the worksheet_change
event (a manual change of the same cell of course did trigger it). Now I
have tried to assign the combobox object to a self made class in which I
try to recognize the change. But the sub objComboBox_Change is only
being triggered during once during the creation phase of the combobox.
.... - multiple problems and no solution - ....



Code:
--------------------
The sheet code :
Public objEvents As New Collection

Private Sub Worksheet_Activate()
Dim rCell As Range
Dim nLastrow, y As Integer
Dim objNewCBO As clsComboBox

'add new checkboxes
nLastrow = LastRow(Worksheets(s_inbox))
Worksheets(s_inbox).Columns(2).ColumnWidth = 20
For y = 1 To nLastrow
Set rCell = Worksheets(s_inbox).cells(y, 2)
rCell.RowHeight = 18
Set objNewCBO = New clsComboBox
Set objNewCBO.objComboBox = Worksheets(s_inbox).OLEObjects.Add("Forms.ComboBox .1", _
Left:=rCell.Left, Top:=rCell.Top, _
Height:=rCell.Height, Width:=rCell.Width).Object
With objNewCBO.objComboBox
.Object.AddItem zRecordStatus1
.Object.AddItem zRecordStatus5
.Object.AddItem zRecordStatus6
.Object.AddItem zRecordStatus7
.LinkedCell = rCell.Address
End With
objEvents.Add objNewCBO
Next y
End Sub
--------------------


My class module clsComboBox :


Code:
--------------------
Public WithEvents objComboBox As MSForms.ComboBox

Private Sub objComboBox_Change()
MsgBox "finally ..."
End Sub
--------------------


anybody that knows the trick ?

thanks in advance,
Goppi


--
Goppi
------------------------------------------------------------------------
Goppi's Profile: http://www.excelforum.com/member.php...o&userid=15823
View this thread: http://www.excelforum.com/showthread...hreadid=273144