VBA Multiple Worksheet Change Events

[mizogy]

Hi All

I'm trying to use two worksheet change events but getting no where fast. I am assuming there can only be one worksheet change executed some the code needs to be migrated into one. Tried several solutions, any help appreciated:

Private Sub Worksheet_Change(ByVal Target As Range)

Static showonceA As Long
If showonceA <> xlOff Then

If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once
If Not Application.Intersect(Target, Me.Range("RCT_Site")) Is Nothing Then    ' indicates the Target range

If Target = "GCSC" Then MsgBox "GCSC has been selected, please remember for all Sourcing and Admin headcount a 1:10 Span of Control (i.e. 10% x FTE) for Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader, Span of Control"
If Target = "Client" Or Target = "Remote" Or Target = "HUB" Then MsgBox "Client / Remote / Hub site location has been selected, please remember for all headcount a Span of Control for Management or Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader & Management, Span of Control"
 
End If
showonceA = xlOff


End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    
Set KeyCells = Range("CountryLocation_RCT")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
           
 Dim rng As Range
    Dim criteria As Variant
    Dim result As Double
    
    Set rng = Range("CountryLocation_RCT")
    criteria = "India"
    
    result = WorksheetFunction.CountIf(rng, ">" & criteria)
    
    If result > 1 Then
   Columns("K:K").Hidden = False
    
        Else
        
    Columns("K:K").Hidden = True
    
    
End If
End Sub

 

[Joe4]

Your second code isn't even valid, as you are missing an "END IF" line at the end.

If you have two working codes that you want to combine, you simply put them both in the same "Worksheet_Change" procedure, one after the other other.
You just have to be mindful of any "Exit Sub" lines you have in there, and may need to edit or remove them.

So, if BOTH of your codes work properly by themselves, you could combine them like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once


'***FIRST BLOCK***
Static showonceA As Long
If showonceA <> xlOff Then
    If Not Application.Intersect(Target, Me.Range("RCT_Site")) Is Nothing Then    ' indicates the Target range
        If Target = "GCSC" Then MsgBox "GCSC has been selected, please remember for all Sourcing and Admin headcount a 1:10 Span of Control (i.e. 10% x FTE) for Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader, Span of Control"
        If Target = "Client" Or Target = "Remote" Or Target = "HUB" Then MsgBox "Client / Remote / Hub site location has been selected, please remember for all headcount a Span of Control for Management or Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader & Management, Span of Control"
    End If
    showonceA = xlOff
End If


'***SECOND BLOCK***
Dim KeyCells As Range
   
Set KeyCells = Range("CountryLocation_RCT")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
          
    Dim rng As Range
    Dim criteria As Variant
    Dim result As Double
   
    Set rng = Range("CountryLocation_RCT")
    criteria = "India"
   
    result = WorksheetFunction.CountIf(rng, ">" & criteria)
   
    If result > 1 Then
        Columns("K:K").Hidden = False
    Else
        Columns("K:K").Hidden = True
      
    End If
End If


End Sub

 

[mizogy]

Your second code isn't even valid, as you are missing an "END IF" line at the end.

If you have two working codes that you want to combine, you simply put them both in the same "Worksheet_Change" procedure, one after the other other.
You just have to be mindful of any "Exit Sub" lines you have in there, and may need to edit or remove them.

So, if BOTH of your codes work properly by themselves, you could combine them like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once


'***FIRST BLOCK***
Static showonceA As Long
If showonceA <> xlOff Then
    If Not Application.Intersect(Target, Me.Range("RCT_Site")) Is Nothing Then    ' indicates the Target range
        If Target = "GCSC" Then MsgBox "GCSC has been selected, please remember for all Sourcing and Admin headcount a 1:10 Span of Control (i.e. 10% x FTE) for Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader, Span of Control"
        If Target = "Client" Or Target = "Remote" Or Target = "HUB" Then MsgBox "Client / Remote / Hub site location has been selected, please remember for all headcount a Span of Control for Management or Team Leader time should be included within a seperate row", vbInformation, "User Information - Team Leader & Management, Span of Control"
    End If
    showonceA = xlOff
End If


'***SECOND BLOCK***
Dim KeyCells As Range
  
Set KeyCells = Range("CountryLocation_RCT")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
         
    Dim rng As Range
    Dim criteria As Variant
    Dim result As Double
  
    Set rng = Range("CountryLocation_RCT")
    criteria = "India"
  
    result = WorksheetFunction.CountIf(rng, ">" & criteria)
  
    If result > 1 Then
        Columns("K:K").Hidden = False
    Else
        Columns("K:K").Hidden = True
     
    End If
End If


End Sub

Thank you @Joe4, appreciated.

 

[Joe4]

You are welcome. Glad I was able to help!