Worksheet_Selectionchange for 2 ranges and outputs

doppleganger

New Member
Joined
Mar 12, 2013
Messages
5
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


Code:
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
maybe like this

Code:
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
 
Upvote 0
thanks for the code hippiehacker

Unfortunately I'm not able to produce any values in the valSelItem cells, any idea on this?
 
Upvote 0
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.
 
Upvote 0
For anyone who comes across this thread with a similar query, I did get assistance on this and the below code works great:


Code:
<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>
 
Upvote 0
i've just put the outcome into the variables valSelItem and not to the named range

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top