capture combobox value chosen , and pass value to macro

[oliver_selwyn]

Hello All and thanks in advance for your help! I am very new to VBA - but experienced with using Excel. I have been trying to build a model which generates x number of identical SalesAgent reports, where x is a number chosen by the user from a combobox. (The reports are identical in contents, but are run for x number of different Agent codes). The maximum value for x is generated by counting the number of rows in a pivottable.

My Countpivrows , ComboBox_Create and ComboBox_InputRange routines work successfully. However any attempt to extract the user chosen value in VBA (Setup Module) fails after 3 days attempts and modifications. (Wide variety of Object type errors).

Upon sucess the number would be passed to the routine generate_21_Agent_tabs_Click() which would run x times. (Right now its bounded to 4 for test purposes).

Public LastRow As Long


Sub cleansheet()
    Application.Goto Sheets("Instructions").Range("A1"), True
    Worksheets("Instructions").Columns(1).ClearContents
    ' Worksheets("Instructions").OLEObjects("Combobox1").Delete
    For i = 0 To 100
        Debug.Print ""
    Next i
End Sub


Sub Countpivrows()
    Application.Goto Sheets("piv").Range("A5"), True
    Dim sht As Worksheet
'    Dim LastRow As Long
    Set sht = ThisWorkbook.Worksheets("piv")
    LastRow = ActiveSheet.PivotTables("Pivottable1").TableRange1.Rows.Count - 2
    Debug.Print "The value of variable LastRow is: " & LastRow
    For i = 1 To LastRow
        ThisWorkbook.Worksheets("Instructions").Range("A" & (1 + i)) = i
    Next i


End Sub


Sub ComboBox_Create()
'PURPOSE: Create a form control combo box and position/size it
Dim Cell As Range
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Instructions")
    Set Cell = Range("C5")
    With Cell
        sht.DropDowns.Add(.Left, .Top, .Width, .Height).Name = "ComboBox1"
    End With
    
    Application.Goto Sheets("Instructions").Range("C5"), True
    
End Sub




Sub ComboBox_InputRange()
'PURPOSE: Add values to your drop down list
    Dim Cell As Range
    Dim sht As Worksheet
    Dim myArray As Variant
    Dim myDropDown As Shape
    
    Set sht = ThisWorkbook.Worksheets("Instructions")
    Set myDropDown = sht.Shapes("ComboBox1")
    
 '   Debug.Print "The value of LastRow is now: " & LastRow
   
    Worksheets("Instructions").Shapes("ComboBox1").ControlFormat.ListFillRange = "A2:A" & LastRow & ""
    Application.Goto Sheets("Instructions").Range("A5"), True


End Sub


Sub SelectedValue()
   MsgBox "Selected Value is" & ComboBox1.Value
'   No matter what I do I cannot get the user chosen value of Combobox1


End Sub

Sub generate_21_Agent_tabs_Click()
Application.Goto Sheets("piv").Range("A5"), True
' Application.Speech.Speak "Hello Tina Selwyn"


Application.Goto Sheets("Agent").Range("A2"), True
Dim i As Integer
For i = 2 To 4
    Sheets("Agent").Select
    Sheets("Agent").Copy After:=Sheets(1)
    ActiveSheet.Name = "Agent_" & i
Next i
Application.Goto Sheets("piv").Range("A5"), True
Dim j As Integer
For j = 4 To 2 Step -1
    Selection.Copy
    Sheets("Agent_" & j).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Name = ActiveSheet.Range("A2")
    Sheets("piv").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
Next j
Application.Calculate
End Sub

 

[Norie]

Where is the combobox located and how did you create it?

Also, how are you running generate_21_Agent_tabs_Click?

 

[oliver_selwyn]

The combobox is located on Sheet1("Instructions") in Cell C5 and is generated with VBA code from the Setups Module. Thanks for asking!

 

[oliver_selwyn]

generate_21_Agent_tabs_Click is literally a Forms.CommandButton now in Sheet1("Instructions"). It used to generate 21 reports - but, the users wish to chose how many reports to run.