+ Reply to Thread
Results 1 to 3 of 3

capture combobox value chosen , and pass value to macro

Hybrid View

oliver_selwyn capture combobox value chosen... 02-04-2016, 07:50 PM
AlphaFrog Re: capture combobox value... 02-04-2016, 09:13 PM
oliver_selwyn Re: capture combobox value... 02-05-2016, 06:12 PM
  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    SD
    MS-Off Ver
    2017
    Posts
    2

    capture combobox value chosen , and pass value to macro

    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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: capture combobox value chosen , and pass value to macro

    Try something like this...

        Dim myDropDown As DropDown
        Set sht = ThisWorkbook.Worksheets("Instructions")
        Set myDropDown = sht.DropDowns("ComboBox1")
    
        If myDropDown.ListIndex > -1 Then
            x = myDropDown.List(myDropDown.ListIndex)
        Else
            MsgBox "Nothing selected in ComboBox1"
        End If
    Last edited by AlphaFrog; 02-04-2016 at 09:17 PM. Reason: Typo: replaced End With with End If
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    12-16-2015
    Location
    SD
    MS-Off Ver
    2017
    Posts
    2

    Re: capture combobox value chosen , and pass value to macro

    Thank you so much, AlphaFrog!!! This worked like a charm! After 3 days worth of attempts you have saved my sanity - and remaining hair.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Capture & Pass Sheet Index
    By dlow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2015, 10:05 PM
  2. Using ComboBox to highlight all chosen selections
    By gtaaccord in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-28-2014, 12:04 PM
  3. [SOLVED] Populate cells when Month is chosen in combobox for a specific name
    By SChapman in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 05-17-2013, 06:14 AM
  4. [SOLVED] ComboBox - select worksheet, pass worksheet name and run macro
    By itcher in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2013, 08:34 AM
  5. [SOLVED] Filling TextBox with data from database when a ComboBox field is chosen
    By alopecito in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2012, 11:34 AM
  6. ComboBox linked to a list of names which when a name is chosen changes a picture ?
    By 1976dan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2012, 05:29 AM
  7. Populating the list in a combobox based off the value chosen in another combobox
    By Mervil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2010, 11:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1