Results 1 to 3 of 3

Thread: How to know which button in group was selected?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    490
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to know which button in group was selected?

    Hi all,

    I have three (3) button in a Group:

    Group Name:
    Sorting

    Button Names:
    FirstNameSortButton
    LastNameSortButton
    LocationSortButton


    Since a ComboBox cannot show more than 1 column when the "ComboBox1_Change()" event fires, I had to overlay a 2 column ListBox on top of my ComboBox so all 3 columns of info can be shown (i.e. Last Name, First Name, Location.)

    Depending on which button in the Group is selected will depend on what order the data is placed into the 3 columns (Column 1 = ComboBox, and Columns 2 & 3 = ListBox.) In a nutshell, I just want the "ComboBox1_Change()" event to display the three columns like it does when the "ComboBox1_Click()" event fires.

    Hopefully someone can assist, and hopefully I didn't lose you with what I need to accomplish.

    Here's what I was trying, but the "Application.Caller" (something I came across in a search) doesn't like my code. I get a Type Mismatch in the Case clause, and I'm not even sure if I can run a Select Case statement within a With statement since the Run-Time Error stopped the code at the type mismatch.

    Code:
    Private Sub ComboBox1_Change()
            
        Dim SelectedRow As Long
        Dim sbSelected As Long
        Dim ws As Worksheet
        Set ws = Sheets("DataSheet")
        SelectedRow = Me.ComboBox1.ListIndex + 2
        
        Select Case Application.Caller
            Case FirstNameSortButton
                sbSelected = 1
            Case LastNameSortButton
                sbSelected = 2
             Case LocationSortButton
                sbSelected = 3
        End Select
        
        With Me.ListBox1
            .Clear
            .ColumnHeads = False
            .ColumnCount = 2
            .ColumnWidths = "186;186"
            .Font.Size = 14
            Select Case sbSelected
                Case 1
                    'FirstNameButton selected
                    'Show Last Name & Location
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 2)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 28)
                 Case 2
                    'LastNameButton selected
                    'Show First Name & Location
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 3)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 28)
                 Case 3
                    'LocationButton selected
                    'Show First Name & Last Name
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 3)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 2)
          End Select
          
       End With
       
    End Sub
    As always, thank you!
    Last edited by TAPS_MikeDion; Oct 14th, 2019 at 11:09 AM.
    Mike Dion

  2. #2
    Board Regular 6StringJazzer's Avatar
    Join Date
    Jan 2010
    Location
    Tysons Corner VA, USA
    Posts
    233
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to know which button in group was selected?

    I am a little unclear as to what order the user is doing this. I'm going to assume these are optionbuttons and the user first selects an optionbutton then selects from the combobox.

    Code:
    Private Sub ComboBox1_Change()
            
        Dim SelectedRow As Long
        Dim sbSelected As Long
        Dim ws As Worksheet
        Set ws = Sheets("DataSheet")
        SelectedRow = Me.ComboBox1.ListIndex + 2
        
        If FirstNameSortButton Then
            sbSelected = 1
        ElseIf LastNameSortButton Then
            sbSelected = 2
        ElseIf LocationSortButton Then
            sbSelected = 3
        End If
        
        With Me.ListBox1
            .Clear
            .ColumnHeads = False
            .ColumnCount = 2
            .ColumnWidths = "186;186"
            .Font.Size = 14
            Select Case sbSelected
                Case 1
                    'FirstNameButton selected
                    'Show Last Name & Location
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 2)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 28)
                 Case 2
                    'LastNameButton selected
                    'Show First Name & Location
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 3)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 28)
                 Case 3
                    'LocationButton selected
                    'Show First Name & Last Name
                    .AddItem ""
                    .List(0, 0) = ws.Cells(SelectedRow, 3)
                    .AddItem ""
                    .List(0, 1) = ws.Cells(SelectedRow, 2)
          End Select
          
       End With
       
    End Sub
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    Use CODE tags to preserve code formatting
    [code]
    ' Your code here
    [/code]

  3. #3
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    490
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to know which button in group was selected?

    Correct assumption. Sorry, forgot to mention that.

    It's always a head-smacker when someone else edits your code and you say to yourself...duh.

    Thanks 6String!
    Mike Dion

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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