A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

worksheetselectionchange function to run macro



 
 
Thread Tools Display Modes
  #1  
Old November 26th, 2006, 10:36 AM posted to microsoft.public.excel.setup
[email protected]
external usenet poster
 
Posts: 1
Default worksheetselectionchange function to run macro

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.

  #2  
Old November 26th, 2006, 10:49 AM posted to microsoft.public.excel.setup
Nick Hodge \(MVP\)
external usenet poster
 
Posts: 2
Default worksheetselectionchange function to run macro

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
DTHIS
www.nickhodge.co.uk


wrote in message
ups.com...
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.


  #3  
Old November 26th, 2006, 10:55 AM posted to microsoft.public.excel.setup
Nick Hodge \(MVP\)
external usenet poster
 
Posts: 2
Default worksheetselectionchange function to run macro

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Nick Hodge (MVP)" wrote in message
...
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
DTHIS
www.nickhodge.co.uk


wrote in message
ups.com...
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.