Worksheet_Selectionchange for 2 ranges and outputs

[doppleganger]

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

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

 

[hippiehacker]

maybe like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range(ActiveCell.Address), Range("rngStructure")) Is Nothing Then Call UpdateAfterAction(Range("rngStructure"), 1)
If Not Intersect(Range(ActiveCell.Address), Range("rngStructure2")) Is Nothing Then Call UpdateAfterAction(Range("rngStructure2"), 2)
End Sub


Sub UpdateAfterAction(strucRange As Range, rangenum As Integer)
    topRow = strucRange.Cells(1, 1).Row
    temp = ActiveCell.Row() - topRow + 1
    
If rangenum = 1 Then valSelItem1 = temp
If rangenum = 2 Then valSelItem2 = temp

End Sub

 

[doppleganger]

thanks for the code hippiehacker

Unfortunately I'm not able to produce any values in the valSelItem cells, any idea on this?

 

[hippiehacker]

what you want to do?

 

[doppleganger]

my first piece of code produced a number according to the selection in the range provided, ie valselitem = 3, it was the third row down on the rngstructure. I need to replicate t​hat for another field and output.

 

[doppleganger]

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.

 

[doppleganger]

For anyone who comes across this thread with a similar query, I did get assistance on this and the below code works great:

<code style="margin: 0px; padding: 0px; font-size: 1em; font-family: Monaco, 'Courier New', monospace;">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</code>

 

[hippiehacker]

i've just put the outcome into the variables valSelItem and not to the named range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range(ActiveCell.Address), Range("rngStructure")) Is Nothing Then Call UpdateAfterAction(Range("rngStructure"), 1)
If Not Intersect(Range(ActiveCell.Address), Range("rngStructure2")) Is Nothing Then Call UpdateAfterAction(Range("rngStructure2"), 2)
End Sub


Sub UpdateAfterAction(strucRange As Range, rangenum As Integer)
    topRow = strucRange.Cells(1, 1).Row
    temp = ActiveCell.Row() - topRow + 1
    
If rangenum = 1 Then Range("valSelItem1") = temp
If rangenum = 2 Then Range("valSelItem2") = temp

End Sub