capture combobox value chosen , and pass value to macro

[oliver_selwyn]

Hello All and thanks in advance for your help! 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.

My Countpivrows , ComboBox_Create and ComboBox_InputRange routines work successfully. The combobox is located on Sheet1("Instructions") in Cell C5 and is generated with VBA code from the Setups Module. However any attempt to extract the user chosen value in VBA (Setup Module) fails .

Upon success the number would be passed to the routine generate_21_Agent_tabs_Click() which would run x times. It is a Forms.CommandButton now in Sheet1("Instructions"). (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 

Share