Combine Worksheet_SelectionChange and Worksheet_Change events

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!

Hi Norie,

That is what I originally thought.
But having searched forums the suggestion seemed to be that a worksheet can only have 1 worksheet change event. This seemed to be confirmed to me by the fact that if I remove one of these events the other one works fine.
Am I missing something?

Apologies maybe I've incorrectly worded my problem...my issue is that I am trying to run these two events but I am finding that not both will run at the same time. if I remove one of the events the other seems to work fine.

Again apologies for my wording.

Cells F10:F227 contain a date and the SelectionChange code assigns any selected cell in this range the name ActiveP. The ActiveP value is then used to calculate the age as of that selected date.

Yes I only want the SelectionChange event to run when the active cell is the range F10:F227. Changes to the cells in this range can be made but the event should only be triggered when there is a change in the active cell.

When both of the event codes are in the worksheet module the selectionchange event seems to be working ok but the worksheet_change event (which is the drop down list multi-select) does not seem to be working at all.

If I remove the selectionchange event the worksheet_change event seems to work perfectly.

I would like the selectionchange event to run when a cell in the range F10:F227 is selected.
So they are as you first stated two different events triggered by two different actions.

Out of curiousity would I be able to use

Please Login or Register  to view this content.

In the workbook module in which to have my selectionchange event for that particular worksheet?

Scrap that idea - it has the same result. Only the selectionchange event runs

Hi Norie,
Please find attached a sample workbook.
Cells B2 and E3 are the ones where I would like to be able to utilise the multi-select worksheet_change event.
Any cells selected in range A5:A20 are then used to provide the ActiveP value.
Following your suggestion of using a break point I noticed when I try to select an additional value from the multiselect the code in the worksheet_change event jumps from "On Error GoTo exitHandler" to the Worksheet_SelectionChange event.

Hi Keebellah - thanks for the contribution!
I've now found that in the worksheet_change event the code jumps from this statement "If Not Intersect(Target, rngDV) Is Nothing Then" to the end of the if statement however removing the "Not" here seems to have the desired affect.
Does anyone know what this statement is saying in lay terms? The combination of the "Not" and "Is Nothing" is a little confusing.

Apologies for the delay managed to get it working, thank you kindly all

I have been experiencing the same problem of "Combine Worksheet_SelectionChange and Worksheet_Change events"
Im fairly new to all this coding and cant seem to find a way to combine them into one. My goal is to have a date picker in any columns i choose and then any DATA validation lists to have multiple selcetions. I can only get one at a time to work. Any guidence in the right direction would be appreciated. I psated my code below (Not sure if I did that properly for this forun.





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 oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If

End If

exitHandler:
Application.EnableEvents = True
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("F4:F50,I4:I50")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With