(ByVal Target As Range) Not Working

MB161

Board Regular
Joined
May 8, 2014
Messages
69
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Where are you passing Target from?
 
Upvote 0
Please forgive me naivety if I've not understood your question... I want Target to be the wholesaler selected from the drop down menu in cell B8. Is it simply a case of assigning this?
 
Upvote 0
I am 99.9% sure the ByVal option is unnecessary and possibly even wrong. It is unclear (to me at least) what exactly this code is doing that it is not supposed to?
You mentioned you believe you know what is going on. Can you elaborate on what this code should do but isn't (or is doing but shouldn't)?
 
Upvote 0
I am guessing that you started from some Event Procedure VBA code (VBA code that is automatically triggered upon some event happening), as that is where you typically see:
(ByVal Target As Range)

The code you have is NOT Event Procedure code, but rather a Sub Procedure you are creating. So Target is NOT automatically defined. If you are including it as a parameter of your Sub Procedure (like you have written), you need to pass in a Range value when you call this Sub Procedure (you will NOT be able to run it as a stand-alone Sub Procedure, as it requires an input, as you have written it).
 
Upvote 0
The code is meant to change the filter on a pivot on another sheet. This works fine but essentially I am trying to put an "on change of cell B8" cue on running the macro.
 
Upvote 0
Target is usually used in event code and is passed to the sub by Excel.

It normally represents the range that has been affected by the event.

For example, this is the code stub for the Worksheet_Change event,
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub
where Target is the range that has been changed on the worksheet.

You can't just add Target as an argument, it doesn't really mean anything.

What I think you should be doing is looking at using the dropdown on the other sheet to trigger the code or perhaps the Worksheet_Change event.
 
Upvote 0
The code is meant to change the filter on a pivot on another sheet. This works fine but essentially I am trying to put an "on change of cell B8" cue on running the macro.
Then you will need to call it from a Worksheet_Change event procedure (note: you CANNOT change the name of an event procedure or else it won't work), passing in the range from the Worksheet_Change event.
 
Upvote 0
Thanks both for your help. I understand the need for the change but am a little confused about how to structure the code.

By looking at a Microsoft help file I have made the changes below. My guess is that this code starts going wrong after the word "Intersect" due to what I am attempting to call. Any kick in the right direction would be a massive help.




Sub Worksheet_Change(ByVal Target As Range)


Dim KeyCells As Range
Set KeyCells = Sheets("Returns Note").Range("B8")


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(KeyCells, Range(Target.Address)) Is Nothing Then
Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Agent Name"). _
ClearAllFilters

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

End If


End Sub
 
Upvote 0
Is the code going wrong?

If it is how? Errors? Unexpected/incorrect results?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top