VBA: LOOP THROUGH EACH COMBOBOX ACTIVE X LIST AND RUN MACRO

Melimob

Active Member
Joined
Oct 16, 2011
Messages
387
Hi,
I currently have a macros which provides a dynamic list of customers to a combo active x control

When user selects the a customer from the dropdown, data updates (formulas) based on that customer.

I have another macro which if run, would create a PDF and email of that data.

I just want to know how to loop for each customer to create the individual pdf's and emails based on the list in the combo box. I.e. as if the user was selecting one then running the macro one by one.

I've seen this on getting the names of customers but not sure how to incorporate it to do what I need?
  1. Sub IterateComboBox()
  2. Dim i As Long
  3. With Sheet1.ComboBox1
  4. For i = 0 To .ListCount - 1
  5. Debug.Print .List(i)
  6. Next
  7. End With
  8. End Sub
Any advice much appreciated.

Many thanks
Melissa
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Melimob

Active Member
Joined
Oct 16, 2011
Messages
387
In case anyone else is struggling .. this code works although I am not an expert so am sure error handling or other would need to be added:

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:

VBA Code:
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:

Code:
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:

Code:
Private Sub Worksheet_Activate()

 Call PT_Intro_LKUP_Filters

End Sub

thank you for your response tho, really appreciated!
Melissa
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,610
Messages
5,765,412
Members
425,285
Latest member
andypandypoo

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
Top