Excel 2007: If a user clicks in Cell 'B4' or... 'E12' in a worksheet, is
there a function or VBA code stub where I can learn what cell they clicked?
Row is needed, row and column is preferred.

I am trying to do a table of DSUM results from a dynamically-updating
database with... 3500 rows or so. I've built a pivot table aggregating the
results of the transactions. Each pivot table row represents a Call Center
Agent and total transactions. I want to extend each row to include
transactions within a specific hour - e.g. Column D=11:00, E=12:00, F=13:00,
etc. I can query the database table to return transactions per a specific
agent for a specific hour

I have the data table and the pivot table of total transactions per agent
working. I do not want to build the table of 55 agents x 24 hours (implying
55 x 24 x 2 criteria), as the agents change day-to-day and hour-to-hour based
on shift. My vision is for the user to click on an agent name, and the
spreadsheet updates the transactions per hour graph of the agent selected.

If I know which cell a user chooses, I only need to build 24 x 2 criteria
for 24 DSUM updates.

Any thoughts?

--Dan

If your plan is to have the user click on a cell, then run macro, could you
use..
xRow = ActiveCell.Row
xColumn = ActiveCell.Column

in VBA, if that wasn't clear.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dan" wrote:

Excel 2007: If a user clicks in Cell 'B4' or... 'E12' in a worksheet, is
there a function or VBA code stub where I can learn what cell they clicked?
Row is needed, row and column is preferred.

I am trying to do a table of DSUM results from a dynamically-updating
database with... 3500 rows or so. I've built a pivot table aggregating the
results of the transactions. Each pivot table row represents a Call Center
Agent and total transactions. I want to extend each row to include
transactions within a specific hour - e.g. Column D=11:00, E=12:00, F=13:00,
etc. I can query the database table to return transactions per a specific
agent for a specific hour

I have the data table and the pivot table of total transactions per agent
working. I do not want to build the table of 55 agents x 24 hours (implying
55 x 24 x 2 criteria), as the agents change day-to-day and hour-to-hour based
on shift. My vision is for the user to click on an agent name, and the
spreadsheet updates the transactions per hour graph of the agent selected.

If I know which cell a user chooses, I only need to build 24 x 2 criteria
for 24 DSUM updates.

Any thoughts?

--Dan

Sub sonic()
MsgBox (ActiveCell.Address)
End Sub

--
Gary''s Student - gsnu200838


"Dan" wrote:

Excel 2007: If a user clicks in Cell 'B4' or... 'E12' in a worksheet, is
there a function or VBA code stub where I can learn what cell they clicked?
Row is needed, row and column is preferred.

I am trying to do a table of DSUM results from a dynamically-updating
database with... 3500 rows or so. I've built a pivot table aggregating the
results of the transactions. Each pivot table row represents a Call Center
Agent and total transactions. I want to extend each row to include
transactions within a specific hour - e.g. Column D=11:00, E=12:00, F=13:00,
etc. I can query the database table to return transactions per a specific
agent for a specific hour

I have the data table and the pivot table of total transactions per agent
working. I do not want to build the table of 55 agents x 24 hours (implying
55 x 24 x 2 criteria), as the agents change day-to-day and hour-to-hour based
on shift. My vision is for the user to click on an agent name, and the
spreadsheet updates the transactions per hour graph of the agent selected.

If I know which cell a user chooses, I only need to build 24 x 2 criteria
for 24 DSUM updates.

Any thoughts?

--Dan

I would like the value of a cell (ex: A1) to change based on which row the
user clicks on.

So... if the user clicks on 'B6', the value of A1 would change to '6' or 'B6'.
I will then use the value of A1 in other cells to construct criteria for a
DSUM.

Hope this helps clarify.

--Dan

"Dan" wrote:

Excel 2007: If a user clicks in Cell 'B4' or... 'E12' in a worksheet, is
there a function or VBA code stub where I can learn what cell they clicked?
Row is needed, row and column is preferred.

I am trying to do a table of DSUM results from a dynamically-updating
database with... 3500 rows or so. I've built a pivot table aggregating the
results of the transactions. Each pivot table row represents a Call Center
Agent and total transactions. I want to extend each row to include
transactions within a specific hour - e.g. Column D=11:00, E=12:00, F=13:00,
etc. I can query the database table to return transactions per a specific
agent for a specific hour

I have the data table and the pivot table of total transactions per agent
working. I do not want to build the table of 55 agents x 24 hours (implying
55 x 24 x 2 criteria), as the agents change day-to-day and hour-to-hour based
on shift. My vision is for the user to click on an agent name, and the
spreadsheet updates the transactions per hour graph of the agent selected.

If I know which cell a user chooses, I only need to build 24 x 2 criteria
for 24 DSUM updates.

Any thoughts?

--Dan

Put the following event code in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Address
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200838


"Dan" wrote:

I would like the value of a cell (ex: A1) to change based on which row the
user clicks on.

So... if the user clicks on 'B6', the value of A1 would change to '6' or 'B6'.
I will then use the value of A1 in other cells to construct criteria for a
DSUM.

Hope this helps clarify.

--Dan

"Dan" wrote:

Excel 2007: If a user clicks in Cell 'B4' or... 'E12' in a worksheet, is
there a function or VBA code stub where I can learn what cell they clicked?
Row is needed, row and column is preferred.

I am trying to do a table of DSUM results from a dynamically-updating
database with... 3500 rows or so. I've built a pivot table aggregating the
results of the transactions. Each pivot table row represents a Call Center
Agent and total transactions. I want to extend each row to include
transactions within a specific hour - e.g. Column D=11:00, E=12:00, F=13:00,
etc. I can query the database table to return transactions per a specific
agent for a specific hour

I have the data table and the pivot table of total transactions per agent
working. I do not want to build the table of 55 agents x 24 hours (implying
55 x 24 x 2 criteria), as the agents change day-to-day and hour-to-hour based
on shift. My vision is for the user to click on an agent name, and the
spreadsheet updates the transactions per hour graph of the agent selected.

If I know which cell a user chooses, I only need to build 24 x 2 criteria
for 24 DSUM updates.

Any thoughts?

--Dan