How do I toggle which Listbox a macro (to move up/down in the listbox) controls?

d0rian

Active Member
Joined
May 30, 2015
Messages
263
I currently have good code (below) that progressively moves the selected row in a Listbox (name: "List Box 301") down by 1 with each run of the macro.

I just added a second Listbox ("List Box 302"), and I want the code to be able to control either Listbox depending on a given cell's value. For example, if $BT$2 = 1, I want the code to control List Box 301. If $BT$2 = 2, I want it to control List Box 302.

Grateful for any help.
VBA Code:
Sub DD_next()
    Dim n%
    With Sheets("scanner").Shapes("List Box 301").ControlFormat
        n = .ListIndex
        If n < .ListCount Then
            .ListIndex = n + 1
        Else
            .ListIndex = 1
        End If
    End With
End Sub
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,967
Perhaps
VBA Code:
Sub DD_next()
    Dim n%
    Dim listName as String

    Select Case Range("BT2").Value
        Case 1
            listName = "List Box 301"
        Case 2
             listName = "List Box 302"
        Case Else
            MsgBox "check the value in $BT$2"
            Exit Sub
    End Select

    With Sheets("scanner").Shapes(listName).ControlFormat
        n = .ListIndex
       .ListIndex = (n Mod .ListCount)+ 1
    End With
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Or ...
VBA Code:
Sub DD_next()
    Dim cf
    Dim n%
    With Sheets("scanner")
        If .Range("$BT$1").value = 1 Then
            Set cf = .Shapes("List Box 302").ControlFormat
        ElseIf .Range("$BT$1").value = 2 Then
            Set cf = .Shapes("List Box 302").ControlFormat
        Else
            MsgBox "Check value in $BT$2", vbExclamation
        End If

        n = cf.ListIndex
        If n < cf.ListCount Then
            cf.ListIndex = n + 1
        Else
            cf.ListIndex = 1
        End If
    End With
End Sub
 

d0rian

Active Member
Joined
May 30, 2015
Messages
263
Perhaps
VBA Code:
Sub DD_next()
    Dim n%
    Dim listName as String

    Select Case Range("BT2").Value
        Case 1
            listName = "List Box 301"
        Case 2
             listName = "List Box 302"
        Case Else
            MsgBox "check the value in $BT$2"
            Exit Sub
    End Select

    With Sheets("scanner").Shapes(listName).ControlFormat
        n = .ListIndex
       .ListIndex = (n Mod .ListCount)+ 1
    End With
End Sub

EDIT: actually hold off on #1 below; i think i might have figured something out.

Thanks, I used this mike, but 2 things:
1) It works, except when the ListBox that's being controlled is in the top-most position; i.e. when the FIRST item in the listbox is selected; when that's the case, the code doesn't do anything.
2) What does the "Exit Sub" line do?
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,967
The Exit Sub line is encountered if there is something other than 1 or 2 in $BT$2. If that is the case, we don't know which list box you want incrimented, so it exits the sub.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,695
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top