Creating event procedure on worksheet
i create a combo box when the user select a cell in a certain range
i want to create an event(change event) so the combo box will have items when the user clicks on the combobox... i inserted some lines in the selection_change and an error "subscript out of range" was shown when I add the event..i can't go debug mode because of the creation of the combo box inside my class...please help me solve it..... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim obj As OLEObject Dim objCombo As ComboBox 'Object For Each obj In ActiveSheet.OLEObjects If obj.Name = "cmbDPS" Then obj.Delete Next 'when beyond DPS column was selected the combobox was still 'created up to the extent of the width of the selected cell If Not Intersect(Target, Range("DPS")) Is Nothing Then Call AutoCom.KeyEventOff Set obj = InsDPS.CreateDropDown(Target) Set objCombo = obj.Object ' 'ActiveSheet.OLEObjects(obj.Object.Name).Change Dim StartLine As Long Dim ws As Worksheet Set ws = ActiveSheet With ActiveWorkbook.VBProject.VBComponents(ws.Name).Cod eModule StartLine = .CreateEventProc("Change", "cmbDPS") '+ 1 .InsertLines StartLine, _ "Msgbox ""Hello World"",vbOkOnly" End With Else AutoCom.KeyEventOn End If If AutoCom.GetValidation Then AutoCom.GetPrevCell.Validation.Delete AutoCom.LetValidation = False End If AutoCom.LetNewCellFlag = True End Sub |
All times are GMT +1. The time now is 06:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com