need programming help with nested event codes
 

Hi,

I have 2 event codes i need to put together. Specifically in column 7 I
need the ability to have multiple events AND to be able to use a dependent
cell (ie show full name but only input the number). I can't figure out how
to insert the dependent cell code so that when I put in a multiple event it
also runs the dependent cell code too. Right now it only does the dependent
cell code for the first value and not the for the succeeding values in my
validation drop down. Your help would be much appreciated.

Thanks (below is the code, I am using codes that I found posted on this
website, THANKS!)

Timber
~~~~~~~~~~~~~~~~~~
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 6 Or 7 Or 8 Or 9 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True


End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 7 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Background").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Background").Range("BCRCOMBO"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub

need programming help with nested event codes
 

Not sure I understand what your problem is, but try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 6 Or 7 Or 8 Or 9 Then
If oldVal < "" Then
If newVal < "" Then
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

If Target.Column = 7 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Background").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Background").Range("BCRCOMBO"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True

End Sub




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"timber" wrote in message
...
Hi,

I have 2 event codes i need to put together. Specifically in column 7 I
need the ability to have multiple events AND to be able to use a dependent
cell (ie show full name but only input the number). I can't figure out
how
to insert the dependent cell code so that when I put in a multiple event
it
also runs the dependent cell code too. Right now it only does the
dependent
cell code for the first value and not the for the succeeding values in my
validation drop down. Your help would be much appreciated.

Thanks (below is the code, I am using codes that I found posted on this
website, THANKS!)

Timber
~~~~~~~~~~~~~~~~~~
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 6 Or 7 Or 8 Or 9 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True


End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 7 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Background").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Background").Range("BCRCOMBO"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub