Hello,
I'm trying to merge a worksheet selection change event and a worksheet change event but don't seem to be getting anywhere. Each one will work without the other fine - but when I try to merge them only the Worksheet_change event works.
The first macro I found on Contextures.com which allows the user to make multiple selections from a drop down list
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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 = 5 And Target.Row = 4) Or (Target.Column = 7 And Target.Row = 8) Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
The second macro allocates the active cell in a particular range the Name "ActiveP". This is then used to work out a persons age on a particular date.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'sub for date selected
Dim myrange As Range 'set variable
On Error Resume Next
Set myrange = Intersect(Range("F10:F227"), Target) 'set target value in defined range to variable myrange
If Not myrange Is Nothing Then 'if cell not selected in defined range then do nothing
ActiveCell.Name = "ActiveP" 'assign the Active Cell the name "ActiveP"
End If
End Sub
I've searched the forums and found examples of multiple Worksheet_change events that have been merged but when I have tried to do something similar to combine these two events I am experiencing an issue where only the worksheet change event works and Excel seems to revert any new dates entered into american date format rather than the default UK format. I have no idea why it would change the date format?
Any suggestions as to how to merge these two events successfully would be greatly welcome!
Bookmarks