limit multi-select list box selections

[supmktg]

I am using a listbox to filter reports. I need to have it act as multi-select in some instances, but have it limited to single selection in others. My research suggests that the multi-select property can only be changed in design view, and therefore can't be changed using vba on the fly.

Does anyone have a creative way to make a multi-select listbox act as if it is not?

Thanks,
Sup

 

[pbaldy]

You're right about the inability to change the setting on the fly. Depending on your situation, you could either disallow a second selection, or test after selections were made and stop if the user should only have made one.

 

[ChrisO]

Perhaps this A2003 demo may be a start…

 

[smig]

you can use two different listBox, showing the correct one each time

 

[pbaldy]

I'm not as fancy as Chris. I did this yesterday to make sure it could be done:

  Dim ctl                     As Control
  Dim intPosition             As Integer
  Dim varItem                 As Variant

  'make listbox single-select based on contents of a textbox
  If Len(Me.Text5 & vbNullString) > 0 Then    'we want it single select
    Set ctl = Me.List2
    If ctl.ItemsSelected.Count > 1 Then
      'get current selection
      intPosition = ctl.ListIndex + IIf(ctl.ColumnHeads, 1, 0)
      'unselect previous selections
      For Each varItem In ctl.ItemsSelected
        ctl.Selected(varItem) = False
      Next varItem
      'reselect last choice
      ctl.Selected(intPosition) = True
    End If
  End If
  Set ctl = Nothing

 

[supmktg]

ChrisO,

Your example does exactly what I want. I really appreciate it!

I almost had it working on the listbox_afterupdate event. I didn't think to use the listbox_mousedown event.

Thanks,
Sup

P.S. the sample is A2K, not 2003

 

[supmktg]

PBaldy,

I was thanking ChrisO while you sent your last reply. Your solution is much appreciated as well!

Thanks,
Sup