Assigning Macro to a cell

[Peter]

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

[Chip Pearson]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[William Seales via OfficeKB.com]

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