Trigger a Macro in another sheet when a cell is changed

[Anthrol]

Hi All,

This community has been a big help after my first post, and now I need some assistance with a function I've been trying to solve (I'm losing hair).

I have a sheet with a few tabs, 'MMenu,' and 'Calendar,' and a macro called 'Fourfivedays.' I want this this macro to trigger in 'Calendar' when I change a date value in a cell in 'MMenu.'

I've tried the basic commands in VBA for 'MMenu,' but I have a feeling this needs to ref the sheet 'Calendar' Somehow. The date cell is E6 and so I tried this,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" Then
With Worksheets ("Calendar")
Call Fourfivedays
End With
End If
End Sub

Perhaps I'm waaay off the mark, I've googled for answers and looked in here too but I can't find one. The code seems to be accepted okay but nothing happens. Your help is most appreciated.

Thanks,

Simon

 

[SteveO59L]

I always have problems with target.address, try

if target.column=5 and target.row=6 then

 

[Anthrol]

Sadly that didn't work.

I always have problems with target.address, try

if target.column=5 and target.row=6 then

 

[Anthrol]

I used this in the end, seems to be a pretty easy way, even though the worksheet briefly flashes. I need to ensure after the cell is updated in MMenu, that the macro is activated in another sheet and the user still remains on the Main Menu. I guess the flash means the code is working.

Sub worksheet_change(ByVal target As Range)
If target.Address = ("$E$6") Then
With Worksheets("Calendar").Activate
Call Fourfivedays
End With
With Worksheets("MMenu").Activate
End With
End If
End Sub