Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: (ByVal Target As Range) Not Working

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default (ByVal Target As Range) Not Working

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,624
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    Where are you passing Target from?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    May 2014
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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?

  4. #4
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,720
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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)?
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,922
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    May 2014
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,624
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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.
    If posting code please use code tags.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,922
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    May 2014
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    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

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,624
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: (ByVal Target As Range) Not Working

    Is the code going wrong?

    If it is how? Errors? Unexpected/incorrect results?
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •