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
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