worksheetselectionchange function to run macro

[sudarshan.phatak]

Hi Gurus

I am not a VB programmer but I know where to write code for worksheet
selectionchange.

I want to run a macro on entry in any cell of column "A". in short I
want a row of formulas to be copied for a raneg (B*.AW*). I don't want
memory to be cosumed by keeping the formulas entered by default.

 

[Nick Hodge \(MVP\)]

Sudarshan

The SelectionChange event has a parameter called Range. This holds the
address (cell, row or column) of the selected cell, so you can use this to
place the formula (I've used Offset and presumed you are storing the
formulae in the range B1:AW1, although of course you could set the formula
of each cell in the row using the WorksheetFunction property of the
Application object)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Range("B1:AW1").Copy Destination:=Target.Offset(0, 1)
End If
End Sub

Now the next issue is how you tell Excel to remove the previous formulae,
when the selection changes? Bear in mind not wanting to have Excel with
loads of formulae may be a false economy as it only saves one copy with
references to the range it is in, so it is very efficient anyway...just a
heads up
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Nick Hodge \(MVP\)]

Eek...sorry a parameter called Target, which is a range object!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk