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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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