If Cell A1 is Active than Type Some Formula in Cell A5

[Legacy 185509]

Hi
Please help me do this
I have over 100 cells if any one is active at one time, it will write different formula in cell A5
if A1 is active it will write may be =2+2
if A3 is active it will write in A5 =S3

and so on....

 

[tweedle]

Something like this, I believe

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A5")
    Select Case Target.Address
    Case Is = "$A$1"
        rng.Formula = "=2+2"
    Case Is = "$A$2"
        rng.Formula = "=S3"
    End Select
End Sub

Right click on the tab and 'View Code' to get to the
Worksheet_SelectionChange(ByVal Target As Range) procedure

 

[Legacy 185509]

Something like this, I believe

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A5")
    Select Case Target.Address
    Case Is = "$A$1"
        rng.Formula = "=2+2"
    Case Is = "$A$2"
        rng.Formula = "=S3"
    End Select
End Sub

Right click on the tab and 'View Code' to get to the
Worksheet_SelectionChange(ByVal Target As Range) procedure

Thanks alot man you are a

 

[Legacy 185509]

any reason why this work on my home computer & not at my work computer
both are same version, both are office 2007
please help

 

[tweedle]

No reason that I am aware of.
This is straight Excel, not even anything version-dependent that I know.


You don't have any code that says...
If Home = True then Works = True Else Works = False
do you?

 

[Legacy 185509]

No reason that I am aware of.
This is straight Excel, not even anything version-dependent that I know.


You don't have any code that says...
If Home = True then Works = True Else Works = False
do you?

no, I tired your code yesterday from home, & it worked on my personal laptop, but I m trying same code on my work computer it doesn't work
any reason why?
here is screenshot of my VB code page

 

[mawdo]

Check events & macros are enabled on your work PC.

Paste the code below into your VBA Immediate Window then press enter

Debug.print Application.EnableEvents

If you get

TRUE

then Events aren't your issue but if you get

FALSE

they are.

You can turn them on again through

Application.EnableEvents = TRUE

but it could be that they are being turned off by some other code...

M

 

[tweedle]

Good thought!

 

[Legacy 185509]

Yes that was the problem. Thanks alot that worked like charm.

 

[Legacy 185509]

Hey I need help again
I modified the code

here is how it looks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Worksheets("Sheet3").Range("A5")
    Select Case Target.Address
    Case Is = "$G$9"
        rng.Formula = "=AD1"
    Case Is = "$G$10"
        rng.Formula = "=AD2"
    Case Is = "$G$11"
     [B]   rng.Formula = "=IV9"[/B]
    End Select
End Sub

I wanna make it like if they click G11 it will write on sheet4 instead of sheet3
it is working fine for G9 & G10, it writes to Sheet3 A5.
thanks