Assigning Macro to a cell
 

How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column title in row 4,
I then want the data in rows 6 to 314 in that same column to be copied to
column D. (From there it feeds through to a report sheet). I have created
the VB for the copying and it runs fine whenever a cell in row 4 is
selected. But I cannot get it to run on its own simply by selecting the
cell.

How is this done please?

Thank you in advance

Peter

Assigning Macro to a cell
 

In the Sheet code module for the appropriate sheet, use the
SelectionChange event procedure. This will execute whenever you
select a cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 4 Then
' your code here
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Peter" wrote in message
...
How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column
title in row 4,
I then want the data in rows 6 to 314 in that same column to be
copied to
column D. (From there it feeds through to a report sheet). I
have created
the VB for the copying and it runs fine whenever a cell in row
4 is
selected. But I cannot get it to run on its own simply by
selecting the
cell.

How is this done please?

Thank you in advance

Peter

Assigning Macro to a cell
 

Suppose you have MyMacro typed in Range("C1"). To run MyMacro by clicking in
the cell C1, place this code in the ThisWorkbook module:


Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Dim sMacro As String

If Not Application.Intersect(Range("C1"), ActiveCell) Is Nothing Then
If Application.ScreenUpdating = True Then
sMacro = Range(Target.Address).Value
Application.Run sMacro
End If
End If

End Sub




Peter wrote:
How can I get a macro to run by clicking in a cell.

I have numerous columns of data. When I click in the column title in row 4,
I then want the data in rows 6 to 314 in that same column to be copied to
column D. (From there it feeds through to a report sheet). I have created
the VB for the copying and it runs fine whenever a cell in row 4 is
selected. But I cannot get it to run on its own simply by selecting the
cell.

How is this done please?

Thank you in advance

Peter