Creating event procedure on worksheet

D

Dantz

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).CodeModule
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top