How to disable items in a listbox?

[feltra]

Hi,

I have a multiselect listbox with about 100+ rows, each row can have
one of 4 different application status (ie. 4 types of rows). Ideally
the user should be allowed to select only one type of row. What is
the best way to achieve this?

Is there a way in Excel VBA to selectively disable specific items in a
listbox? Something like greying out the row or removing the option
button for that row will do nicely, i think.

The workaround I use right now is cludgy - I display 2 listboxes - one
for the selectable entries and the other for the all the other type of
rows... Needless to say I am totally unhappy with this approach.

If you have any other solution also (other than using listboxes),
please let know

Thanks in advance for any & all help,
-feltra

 

[Nigel]

You cannot disable selected rows, but you can selectively remove items that
are no longer required. The confusion for the user is that the list
suddenly changes and of course if they change their mind the other options
are no longer present. This of course is also true if you could disable
selected rows. 100+ rows is long list for a user to scroll !

You might use option buttons (radio) buttons to select the type then fill
the list with the relevant list. Maybe easy than using two list boxes and
keeps list short.

If you need code let me know.

 

[feltra]

Hi Nigel,

Thanks for replying.

Unfortunately, it will not fill my need. I need to display ALL the
rows, but not allow the user to select the other 3 types of rows (ie.
the user needs to know the status of the other rows). I don't need
the user to be able to decide which items can be selected (that is
decided programatically), so *while* the user is browsing the list and
making up his mind as to which items should be selected, the list
entries will not change (ie. no surprises to user while browsing the
list).

I also wondered but could not implement the following workaround: If
the user clicks on one of the other 3 types (ie one which he should
not be selecting), I will force the option button to become unchecked
and display some status bar message. The reason I could not do it is
because control simply does not come into the Listbox_Click() event
handler, if the selection type is MultiSelect. It comes into the
event handler only for SingleSelect - and I need to have MultiSelect
due to the huge number of rows.


Thanks & Best Regards,
-feltra

 

[Andy Pope]

Hi,

You could try this. Listbox is populated with content of A1:A26 of the
activesheet.
Create a userform with a list box. You will only be allow to select every
other item.

'-------------------------------
Private m_blnChanging As Boolean
Private Sub ListBox1_Change()
Dim lngIndex As Long

If Not m_blnChanging Then
m_blnChanging = True
For lngIndex = 0 To ListBox1.ListCount - 1
If lngIndex Mod 2 = 0 Then
ListBox1.Selected(lngIndex) = False
End If
Next
m_blnChanging = False
End If

End Sub

Private Sub UserForm_Initialize()

m_blnChanging = True
ListBox1.RowSource = "A1:A26"
ListBox1.MultiSelect = fmMultiSelectMulti
m_blnChanging = False

End Sub
'------------------------
cheers
Andy