Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TRICKY: runtime generated comboboxes and change event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TRICKY: runtime generated comboboxes and change event
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 Goppi wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox added to Form at runtime too small, can't control size andchange event won't trigger | Excel Worksheet Functions | |||
Textbox added to Form at runtime too small, can't control size andchange event won't trigger | Excel Worksheet Functions | |||
User forms that are generated at runtime | Excel Programming | |||
change event/after update event?? | Excel Programming | |||
How to code event for dynamically generated checkboxes | Excel Programming |