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
Bookmarks