TRICKY: runtime generated comboboxes and change event

[Goppi]

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 objNewCBbjComboBox = Worksheets(s_inbox).OLEObjects.Add("Forms.ComboBox.1", _
Left:=rCell.Left, Top:=rCell.Top, _
Height:=rCell.Height, Width:=rCell.Width).Object
With objNewCBbjComboBox
.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

 

[keepITcool]

Goppi,

PLEASE rethink your approach...

(besides the fact that you keep adding controls and i dont
see the code for cleaning or checking if combos exist...)

a sheet with 1000 rows will get 1000 embedded comboboxes.
=> now look in the temp folder... (windows Start/Run %temp%

you'll have added 1000 tmp (emf) files.

nope: this is NOT the way to go.

embedded controls are "nice".. but they are hogging the system.
and will make your workbook very unstable if the row count goes up.

IF you need controls on a sheet use the "native" controls from the
"Forms toolbar"


ARE you sure you cannot achieve the same result with Data Validation?
combine it with a change event handler on a range (either in worksheet,
workbook or even application object and i'm sure it will be lot's
faster, stabler and easier to code.





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >