Multi Select but only allow 2 to be selected

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
I have a userform with a multi select listbox.

however i would like to customize how it works

1. i would like to only allow one or two entries to be selected no more

a msgbox that says "you can only select one or two Dates" then clear the last selected would be great.

Below is the code so far that enters the forst selected date in Range("AvailableDates1") and then the next in next row down etc


Code:
Private Sub CommandButton1_Click()
Dim i As Long, ACell As Range
Set ACell = Range("AvailableDates1")
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        ACell = ListBox1.List(i)
        Set ACell = ACell.Offset(1, 0)
    End If
Next i
End Sub

anyone help me restrict the select operation to one or two only would be great
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Code:
Private Sub CommandButton1_Click()
    Dim i As Long, ACell As Range, counter As Byte
    Set ACell = Range("AvailableDates1")
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            counter = counter + 1
            Select Case counter
                Case 1: ACell = ListBox1.List(i)
                Case 2: ACell.Offset(1) = ListBox1.List(i)
                Case Is > 2: ListBox1.Selected(i) = False
            End Select
        End If
    Next i
    If counter > 2 Then MsgBox "Only the first two selections were used.", vbInformation, "Two Selections Only"
End Sub
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,147
Messages
5,835,673
Members
430,374
Latest member
PeteBrown

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