Multiple Worksheet Change Events - Excel VBA

[mizogy]

Hi All

I am having trouble combining two worksheet change event codes into one. Either event could happen whilst they are not dependant on each other. Can you combine multiple worsheet change events based on independant cell changes. I've included the two macros below which I need to combine into one:

Macro 1

Private Sub worksheet_change1(ByVal target As Range)
If Not Intersect(target, Range("F12:F58")) Is Nothing Then
Dim a As Variant
a = Range("AQ11")
If a = 0 Then
MsgBox "Country selected has no salary data to base an indexation, therefore data is unstable to use", vbExclamation, "Information Alert"
End If
End If
End Sub

Macro 2

Private Sub worksheet_change(ByVal target As Range)
Dim KeyCell As Range
Set KeyCell = Range("E8")

If Not Application.Intersect(KeyCell, Range(target.Address)) Is Nothing Then
If KeyCell = "Yes" Then

Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = False

Range("A1").Select
Application.Goto Reference:="R1C1"
MsgBox "With Inflation option has been selected", vbExclamation, "Information Alert"

Application.Goto Reference:="R1C1"

Else

Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = True

Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = False
Range("A1").Select
Application.Goto Reference:="R1C1"
MsgBox "Without Inflation option has been selected", vbExclamation, "Information Alert"


End If
End If

End Sub

 

[Jonmo1]

Welcome to the board....


Make sure Macro1 is written above Macro2
And no other code is written between Macro1 and Macro2.

Delete this line from Macro1
End Sub

Delete this line from Macro2
Private Sub worksheet_change(ByVal target As Range)

 

[mizogy]

Hi Jonmo / All

Thanks for your reply. I've made the amends as suggested but does not trigger any event which it should albeit the macro does not error / fallover. Any suggestions

Amended macro now reads:


Private Sub worksheet_change1(ByVal target As Range)
If Not Intersect(target, Range("F12:F58")) Is Nothing Then
Dim a As Variant
a = Range("AQ11")
If a = 0 Then
MsgBox "Country selected has no salary data to base an indexation, therefore data is unstable to use", vbExclamation, "Information Alert"
End If
End If
Dim KeyCell As Range
Set KeyCell = Range("E8")

If Not Application.Intersect(KeyCell, Range(target.Address)) Is Nothing Then
If KeyCell = "Yes" Then


Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = False

Range("A1").Select
Application.Goto Reference:="R1C1"
MsgBox "With Inflation option has been selected", vbExclamation, "Information Alert"

Application.Goto Reference:="R1C1"

Else

Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = True

Application.Goto Reference:="BasicWithInflation"
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="BasicWithoutInflation"
Selection.EntireColumn.Hidden = False
Range("A1").Select
Application.Goto Reference:="R1C1"
MsgBox "Without Inflation option has been selected", vbExclamation, "Information Alert"


End If
End If

End Sub

 

[Jonmo1]

Change
Private Sub worksheet_change1(ByVal target As Range)
to
Private Sub worksheet_change(ByVal target As Range)

 

[mizogy]

Many thanks Jonmo, all working now - your help much appreciated. Cheers, Mizogy.

 

[Jonmo1]

Glad to help, thanks for the feedback.