Running Macro when a cell is clicked

[EliseFreedman]

Hi There

I have a spreadsheet which brings together various totals from some underlying spreadsheets. Initially I had used countifs to display the totals on the summary spreadsheet. However, following a meeting with a couple of other colleagues this morning I need to change my approach. The stakeholders are keen to be able to click on the totals in the summary spreadsheet and view the underlying data. It has been suggested that rather than using countifs, I use pivot tables in a hidden sheet and then use getpivotdata to get the totals to display in the summary sheet. That is straightforward enough. However, the bit I am struggling with is adding the functionality to be able to click on the value in the summary sheet and display the underlying data. I have created a macro which determines which row they have clicked and then used that row in the filter to find the matching data in the underlying spreadsheet. This works perfectly if I select a cell and then run the macro manually.

However, I would like the macro to fire anytime the user clicks on the cell within the column. Seems straightforward enough but nothing happens when I click on any of the cells. It just shows the formula that is within the cell. Can anyone tell me what I am doing wrong

I have tried the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 10 Then
         Call SelectTeam() 
     Else
     End If
End Sub

Thanks in Advance

 

[SkipVought]

Hi,

Exactly where does your [tt]Worksheet_SelectionChange[/tt] reside?

It must reside in the SHEET CODE MODULE for that specific sheet (right-click the sheet tab and select View Code)

All your posted code does is call a procedure, whatever it does and that only happens when you select in column J.

And this would be better, depending upon whatbyour procedure does...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Count = 1
      If Target.Column = 10 And Len(Target.Value) <> 0 Then
         Call SelectTeam() 
      End If
    End If
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[remeng]

Hi,

Maybe I am reading your issue incorrectly, but with pivot tables, if you double click a cell such as a total, it will open the raw data in another sheet.

This article may help:
Link

Mike

 

[SkipVought]

The stakeholders are keen to be able to click on the totals in the summary spreadsheet and view the underlying data.

Well the devil is in the details. How is your summary sheet assembled? I’d guess that to get to the sheet where the data resides and determine what criteria may be involved, might take other data in the row of the selection and may even require other lookups based on those values to derive the parameters required to produce a data subset for the desired report.

Might need to see a representative portion of your workbook in order to help you develope a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Hey, Elsie, see you’re back on Tek-Tips.

What happened here?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein