Excel VBA - Worksheet_Selectionchange for 2 ranges and outputs

[crownedking]

The below macro allows users to select a cell from the named range "rngStructure" and produce a value in "valSelItem". This value I use to dynamically drive a dashboard.


I now need to expand this dashboard to include a 2nd variable (output) "valSelItem2" from a 2nd range "rngStructure2", however I'm not sure how to incorporate this 2nd requirement into my current macro.


The end result from a user perspective is selection of "rngStructure" narrows down the dataset, while the selection of another cell within "rngStructure2" allows that dataset to be further refined in the dashboard.


Any assistance would be appreciated


Cheers

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not _
(Application.Intersect(ActiveCell, Range("rngStructure").Cells) _
Is Nothing) Then _
Call UpdateAfterAction
End Sub

Sub UpdateAfterAction()
Dim topRow As Integer

topRow = Range("rngStructure").Cells(1, 1).Row
[valSelItem] = ActiveCell.Row() - topRow + 1
End Sub

[/pre]

 

[crownedking]

I've uploaded an example file here https://www.dropbox.com/s/ujjrdw2e76oksqm/Example.xlsm


This file runs off my original code, as you can see, rngStructure provides valSelItem1 with a value according to user selection. rnStructure2 only fires on worksheet_change given thats what I'm using at the moment, I want this to operate in the same manner as rngStructure.

 

[Hui]

I made a number of small changes throughout the 4 modules

You don't need the change event code at all, so I removed it

See below:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

If Not _
(Application.Intersect(ActiveCell, Range("rngStructure").Cells) _
Is Nothing) Then _
Call UpdateAfterAction

If Not _
(Application.Intersect(ActiveCell, Range("rngStructure2").Cells) _
Is Nothing) Then _
Call UpdateAfterAction_1

Application.EnableEvents = True
End Sub

Sub UpdateAfterAction()
Dim topRow As Integer

topRow = Range("rngStructure").Cells(1, 1).Row
[valSelItem1] = ActiveCell.Row - topRow + 1
End Sub

Sub UpdateAfterAction_1()
Dim topRow As Integer

topRow = Range("rngStructure2").Cells(1, 1).Row
[valSelItem2] = ActiveCell.Row - topRow + 1
End Sub

[/pre]

 

[crownedking]

Thanks Hui, works a treat. I initially used the change event code to have some functionality in the dashboard, until I better understood how to incorporate the selectionchange.


Thanks again!