×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Running Macro when a cell is clicked

Running Macro when a cell is clicked

Running Macro when a cell is clicked

(OP)
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

CODE

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

Thanks in Advance

RE: Running Macro when a cell is clicked

Hi,

Exactly where does your Worksheet_SelectionChange 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...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Running Macro when a cell is clicked

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

RE: Running Macro when a cell is clicked

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Running Macro when a cell is clicked

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

What happened here?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close