Event Trigger - EXCEL VBA

[C Brandt]

I have a very large table and I would like to trigger a macro when one of
the cells is selected and use the data in the horizontal & vertical headers
as input to the routine to create a chart.
Two key pieces of information that I need to get strated are:
1) Creating the trigger for the macro when the cell is selected and
2) deteriming the cell location so that I can pull the two hearder cells.

Can this be done and if so, how do you set this up.

Thanks for your help,
Craig

 

[Earl Kiosterud]

C,

Put the following routine in the sheet module for the sheet containing the table. It will
get executed automatically when the selection is changed, and variables ColumnHeader and
RowHeader will contain the values in the table headers. Now either call your routine (in a
general module), or do your processing right there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ColumnHeader
Dim RowHeader
ColumnHeader = Cells(1, Target.Column).Value
RowHeader = Cells(Target.Row, 1).Value
MsgBox ColumnHeader & " " & RowHeader
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Dave Peterson]

You can use something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'one cell at a time
If Target.Cells > 1 Then Exit Sub

'only a cell in the table range (I used a1:x99)
If Intersect(Target, Me.Range("A1:x99")) Is Nothing Then Exit Sub

MsgBox Me.Cells(1, Target.Column).Address & " <-- column header" _
& vbLf & Me.Cells(Target.Row, "A").Address & " <-- row header"

End Sub

 

[C Brandt]

Thanks Guys.

I'm on my way.

Craig