(ByVal Target As Range) Not Working

[MB161]

Afternoon,

I am trying to get the filter on a pivot to change to the wholesaler selected from a drop down menu on another sheet. (There are some slight format changes between the list of wholesalers the selection is made from and their labelling in the raw data which the pivot accesses, hence the lookup). The code below works when ran manually but I wanted to automate the process by using (ByVal Target As Range). I think I understand where it is going wrong but I'm not sure which changes I need to make to get it working.

Any help would be greatly appreciated!

Sub PivotChange(ByVal Target As Range)


Table1 = Sheets("Acrynyms").Range("D2:F24")

On Error Resume Next
Result = Application.WorksheetFunction.VLookup(Sheets("Returns Note").Range("B8"), Table1, 3, False)
If Err <> 0 Then
Result = xlErrNA
End If


If Not Application.Intersect(Target, Sheets("Returns Note").Range("B8")) Is Nothing Then
Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name"). _
ClearAllFilters
Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage _
= Result
End If


End Sub

 

[MB161]

At the risk of making everything a little more complicated I was hoping to add another WorkSheet_Change procedure to the same sheet. I know this is not possible as each sheet can only have one so would it be possible to combine to two following procedures into one piece of code? The new event procedure is meant to check that the next pallet number, entered at the bottom of column E is the next one in the sequence.

Sub WorkSheet_Change(ByVal Target As Range)
Dim Result As Variant
Dim KeyCells As Range
Dim Table1 As Range
    
    Set KeyCells = Sheets("Returns Note").Range("B8")
    Set Table1 = Sheets("Acrynyms").Range("D2:F24")
 
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then


        Result = Application.VLookup(Target, Table1, 3, False)


        If IsError(Result) Then Exit Sub


        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").ClearAllFilters
        Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name").CurrentPage = Result


    End If


End Sub


Sub WorkSheet_Change(ByVal Target As Range)
Dim PalletRange As Range
Dim MaxVal As Integer


    If Not Application.Intersect(PalletRange, Target) Is Nothing Then


        ActiveCell.Offset(-1, 0).Select
        Set PalletRange = Range(ActiveCell.Offset(-1, 0), "E11")
        
        MaxVal = Application.Max(PalletRange)
    
        If Not ActiveCell.Value - 1 = MaxVal Then
        
            ActiveCell.Value = ""
            MsgBox ("Please check that you have not missed out a pallet")
        
        Else
        
            ActiveCell.Offset(0, -1).Select
          
        End If
        
    End If
           
End Sub

 

[MB161]

Ignoring my previous post please could anyone tell me why the following code doesn't give the desired output/ appear to do anything when a change in the worksheet is made.

Thanks in advance for any help.

Sub WorkSheet_Change(ByVal Target As Range)
Dim Result As Variant
Dim KeyCells As Range
Dim Table1 As Range
Dim PalletRange As Range
Dim MaxVal As Integer


If ActiveCell.Column = 5 Then
   
    Set PalletRange = Range(ActiveCell.Offset(-2, 0), "E11")
    
        Target.Select
                                  
        MaxVal = Application.Max(PalletRange)
            
            If Application.Intersect(PalletRange, Target) Is Nothing Then
            
                If Target.Value = "" Then
                
                    MsgBox ("Cell above is blank")
                    
                    Else
                
                        If Not Target.Value - 1 = MaxVal Then
                            
                            If Not Target.Value <= MaxVal Then
                                
                                ActiveCell.Offset(1, 0).Value = ""
                                MsgBox ("Please check that you have not missed out a pallet")
              
                            End If
                            
                        Else
                            
                            ActiveCell.Offset(0, -1).Select
                              
                        End If
                    
                End If
                
            End If
            
End If
           
End Sub

 

[Joe4]

You probably seldom ever want to use "ActiveCell" or "Select" in Worksheet_Change event procedures. By default, Target is the Active Cell, so there is no need to use ActiveCell or Select it. To reference other cells relative to Target, you would typically use Offset, or use Cell(row, column) referencing.

could anyone tell me why the following code doesn't give the desired output/ appear to do anything when a change in the worksheet is made

Can you tell us what it is supposed to do, and what you are doing that triggers it (what cell you are updating with what value), and what does the data it is referencing looks like?

 

[MB161]

Apologies for the delay.

In short the code is meant to check that any number added at the bottom of the range in column E is either 1 higher than the max value already in the range or lower. The trigger is completing the input although I realise that my current code assumes that enter has been pressed after typing and not tab.

Also am I correct in thinking that by adding several column checks, like at the start of the code, I can add the code we generated to update the pivot filter to the code and both event based procedures will work?

 

[MB161]

I made the following changes and got the code to work.

Sub Worksheet_Change(ByVal Target As Range)


Dim Result As Variant
Dim KeyCells As Range
Dim Table1 As Range
Dim PalletRange As Range
Dim MaxVal As Integer


If Target.Column = 5 Then
   
    Application.EnableEvents = False
       
    If Target.Offset(-1, 0).Value = "" Then
            
        Target.Value = ""
        MsgBox ("Cell above is blank")
        Target.Offset(-1, 0).Select
                
    Else
    
        Set PalletRange = Range("E11", Target.Offset(-2, 0))
        MaxVal = Application.Max(PalletRange)
                                        
        If Not Target.Value - 1 = MaxVal Then
                                    
            If Not Target.Value <= MaxVal Then
                                        
                Target.Value = ""
                Target.Select
                MsgBox ("Please check that you have not missed out a pallet")
                      
            End If
                                    
        Else
                                    
        Target.Offset(0, -1).Select
                                      
        End If
                                        
    End If


End If


Application.EnableEvents = True
           
End Sub

 

[Joe4]

I made the following changes and got the code to work.

Great!