VBA TYPE MISMATCH

[Melimob]

Hi - I seem to have swapped one error for another.

Objective: Combobox ActiveX dropdown > once clicked changes filters on PivotTable.

I have looked at code and got issue on '1004 error" when attempting to set the "CurrentPage" property of the PivotField class.

I searched and recorded a macro and ended up with the below amendments but now am getting 'Type Mismatch'.

I feel I am really close.. any advice greatly received.

FYI - I need to set 3 filters based on 1 combox activex box (which uses data in data model in PivotTable) and 2 x normal drop downs. The PowerPivot is linked to cell R1 hence that's the cell I'm referencing as change.

Option Explicit

Sub ChangePiv()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim str As String


 Set pt = Sheet21.PivotTables("PivotTable5")
Set pf = pt.PivotFields("[LeadData].[Introducer (Actual)].[Introducer (Actual)]")
 str = Array("[LeadData].[Introducer (Actual)].&[" & Worksheets("INTRODUCER DASHBOARD").Range("R1").Value & "]")


With pt
 pf.ClearAllFilters
 pf.VisibleItemsList = str
  End With
 

End Sub

Many thanks
Melissa

 

[jasonb75]

Could be something else that I'm not seeing but assigning an array to a string variable is one reason for the error. Arrays need to be assigned to variants, although you only have a single item in the array so in theory you could use str = "[LeadData].[Introducer (Actual)].&[" & Worksheets("INTRODUCER DASHBOARD").Range("R1").Value & "]"

 

[Melimob]

Hi Jason

Thanks so much for responding.. as you can tell I am *attempting* to learn VBA just by trial and error.

Fortunately I have SOLVED it!.. probably not the right or cleanest way but it works.

this is what I have done:

1. When combobox options are changed the worksheet change code calls this macro which is saved as a module:

Option Explicit

Sub ChangePiv()
 
Dim PT1 As PivotTable
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim Choice1 As String
Dim Choice2 As String
Dim Choice3 As String


Set PT1 = Worksheets("INTRODUCER DASHBOARD").PivotTables("PivotTable5")
Set PF1 = PT1.PivotFields("[LeadData].[Introducer (Actual)].[Introducer (Actual)]")
Set PF2 = PT1.PivotFields("[LeadData].[Lead Month].[Lead Month]")
Set PF3 = PT1.PivotFields("[LeadData].[Lead Year].[Lead Year]")

Choice1 = Worksheets("INTRODUCER DASHBOARD").Range("R1").Value
Choice2 = Worksheets("INTRODUCER DASHBOARD").Range("C3").Value
Choice3 = Worksheets("INTRODUCER DASHBOARD").Range("C4").Value


With PT1
  PF1.CurrentPageName = "[LeadData].[Introducer (Actual)].&[" & Choice1 & "]"
  PF2.CurrentPageName = "[LeadData].[Lead Month].&[" & Choice2 & "]"
  PF3.CurrentPageName = "[LeadData].[Lead Year].&[" & Choice3 & "]"
 
End With

End Sub

2. if the month or year dropdowns are changed it calls these worksheet macros:

Private Sub ComboBox1_Change()
ChangePiv

End Sub
    

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub
 
Dim PT1 As PivotTable
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim Choice1 As String
Dim Choice2 As String
Dim Choice3 As String


Set PT1 = Worksheets("INTRODUCER DASHBOARD").PivotTables("PivotTable5")
Set PF1 = PT1.PivotFields("[LeadData].[Introducer (Actual)].[Introducer (Actual)]")
Set PF2 = PT1.PivotFields("[LeadData].[Lead Month].[Lead Month]")
Set PF3 = PT1.PivotFields("[LeadData].[Lead Year].[Lead Year]")

Choice1 = Worksheets("INTRODUCER DASHBOARD").Range("R1").Value
Choice2 = Worksheets("INTRODUCER DASHBOARD").Range("C3").Value
Choice3 = Worksheets("INTRODUCER DASHBOARD").Range("C4").Value


With PT1
  PF1.CurrentPageName = "[LeadData].[Introducer (Actual)].&[" & Choice1 & "]"
  PF2.CurrentPageName = "[LeadData].[Lead Month].&[" & Choice2 & "]"
  PF3.CurrentPageName = "[LeadData].[Lead Year].&[" & Choice3 & "]"
 
End With

End Sub

3. this code is also on worksheet to update the combobox active x list named range:

Private Sub Worksheet_Activate()

 Call PT_Intro_LKUP_Filters

End Sub

thank you for your response tho, really appreciated!
Melissa

 

[jasonb75]

I am *attempting* to learn VBA just by trial and error.

That's all I ever do

probably not the right or cleanest way but it works.

I don't think there is such a thing as 'the right way', although you will likely find that some methods will be cleaner / more efficient than others.

Three things to consider while you're learning.
Do you understand how your code works? If you understand it then you can always make changes to it and possibly improve it as you learn more.
Does it work as required? The important bit.
Does it do what you want in a reasonable time? There are some things that will slow your code down and others that can speed it up if there is a noticeable delay in the run time. Recorded code will almost always include Select and Selection, which are not necessary and make it much slower, but might make it easier to follow what is happening while you're learning basics.

 

[Melimob]

thanks again for all your advice Jason! very true and much appreciated!
Now.. I'm going to post another thread which I can't figure... loop through (For each) to run the macro for each value in the drop down. i.e. instead of user selecting one by one and hitting the macro.