Sheet change event

[ajaar]

Hi friends,
I am trying to achieve a sheet change event not succeeded. for example when I change value in D7 of Sheet Entry, I wanted to change the F10 of Sheet Calculation to the same value in D7. file is attached for your reference. Appreciate any help

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "D7" Then
  Sheets("Calculation").Range("F10").Formula = "=Entry!D7"
Else
If Target.Address = "D8" Then
  Sheets("Calculation").Range("B11").Formula = "=Entry!D8"
Else
If Target.Address = "D9" Then
  Sheets("Calculation").Range("F16").Formula = "=Entry!D9"
Else
If Target.Address = "D13" Then
  Sheets("Calculation").Range("F24").Formula = "=Entry!D13"
Else
If Target.Address = "D14" Then
  Sheets("Calculation").Range("C37").Formula = "=Entry!D14"

End If
End If
End If
End If
End If
End Sub

 

[shrivallabha]

If the cells are fixed then you really do not need an event to control this. e.g. If you go to cell F10 and write following formula:
=Entry!D7

Then the data will get updated every time you make changes to entry sheet. If you fear that the users might change some of these cells then protect those cells using worksheet protect.

 

[Somendra Misra]

Hi,

Put absolute ref. ig you code like below.

If Target.Address = "$D$7" Then

And change for rest also.

Regards,

 

[ajaar]

Dear Somendra,

Surprising... thank you... it works well.

How come it is not working without ' $ '. in the formula it only make cell address fixed as of I know.

Thanks you again.

Regards
Ajaar

 

[Somendra Misra]

@ajaar

When you say Target.Address VBA take it as $D$7 and then you are comparing it with D7, so they are not equal. That is the reason.

Regards,

 

[shrivallabha]

Target.Address if used as it is then uses default arguments for absolute address as True.

e.g. following will work

If Target.Address(False, False) = "D7" Then

Instead of writing IF you could also use Select Case statement as below:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address(False, False)
Case "D7"
  Sheets("Calculation").Range("F10").Formula = "=Entry!D7"
Case "D8"
  Sheets("Calculation").Range("B11").Formula = "=Entry!D8"
Case "D9"
  Sheets("Calculation").Range("F16").Formula = "=Entry!D9"
Case "D13"
  Sheets("Calculation").Range("F24").Formula = "=Entry!D13"
Case "D14"
  Sheets("Calculation").Range("C37").Formula = "=Entry!D14"
End Select

End Sub

 

[jeffreyweir]

Thowing my two cents into the mix: NEVER refer to ranges in VBA by using cell addresses like "$D$7" or "D7". As soon as you add another row in the workbook, you break the code, because those references don't update in VBA.

Instead, ALWAYS set up named ranges in the workbook, and refer to those named ranges in your code.

PrivateSub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Range("SomeNamedRange1").Address
  Range("SomeTarget1").Formula = "=SomeOtherNamedRange1"

Case Range("SomeNamedRange2").Address
  Range("SomeTarget2").Formula = "=SomeOtherNamedRange2"

End Select

EndSub

 

[ajaar]

Thanks All for your responses.