Greetings to all,
I have a workbook (Excel 2003) with several forms with several
listboxes. After I protected my VBA Project, I was unable to select
anything in about half of the listboxes. It would put the dashed
highlighted box around it but wouldn't turn shaded like usual, when
OK
was pressed, nothing was selected. Often if I close the workbook and
then reopen it, the problem will go away temporarily.
This brought me to adding the ListBox1.ListIndex = 0 to the
initialize
sub. Here again, this only seemed to select the first item in about
half of the listboxes/forms. This is consistent whether my VBA
Project is protected or not.
To make things worse, the check to see if an item was selected fails
also (ListIndex = -1), so the program tries to run without a
selection
and bad things happen.
I have listed the code for one of the problem forms below and any
help
or insight would be much appreciated. I have tried adding the form
name before ListBox1 to no avail.
Thanks! I'm happy to forward the spreadsheet if that would help.
Private Sub UserForm_Initialize()
Dim i As Integer
Dim EquipCount As Integer
'retrieve stored counters
EquipCount = Range("EquipCount").Value
'make list alphabetic
Range(Cells(10, 1), Cells(EquipCount + 9, 7)).Sort _
Key1:=Cells(10, 1), Order1:=xlAscending, Header:=xlNo
'build lists
For i = 1 To EquipCount
ListBox1.AddItem (Cells(i + 9, 1).Value)
Next i
ListBox1.ListIndex = 0
End Sub
Private Sub OKButton_Click()
Application.ScreenUpdating = False
Dim r As Integer 'remove list
'make sure equipment was selected
If ListBox1.ListIndex = -1 Then
MsgBox "You must select a name from the list."
Exit Sub
End If
'delete equipment for each selection
For r = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(r) Then
Range("G1") = ListBox1.List(r)
Call DeleteEquip
Sheets("Data").Activate
Range("G1").ClearContents
End If
Next r
'housekeeping
Sheets("Data").Range("G1").ClearContents
Unload DeleteEquipForm
End Sub
|
Similar Threads
