Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
Creating An Event Procedure | Excel Programming | |||
Creating an event procedure question | Excel Programming | |||
Running Event Procedure When Cell Updated on Excel Worksheet | Excel Programming | |||
Using VBA to track changes in a worksheet - help! Event procedure?? | Excel Programming |