Listbox scroll operation gives duplicate rows.
Sanjay
I seem to have a very strange problem.
I have a button on the worksheet of the Excel.Upon
clicking the button I show a VB form which has a listbox
containing about 15-20 entries in it.To scroll through all
the contents of the list box I obviously have the scroll
button for the list box which comes by default.
I also have two buttons on the VB form for "MoveUp"
and "MoveDown" actions for the items in the list box.
The problem arises when I select a particular item in
the list and move it up or down in the list.
For Eg:Assume there are 10 items in the list.5 are
visible.To see the other 5 we have to scroll down.Now i
select item 2 in the list box and move it down the list
using "MoveDown" button.The operation works fine till it
reaches the 5th position in the box.Now when I move it
down again,the 6th item moves up and the 5th item
mysteriously displays the 6th item itself,ie,I have
duplicate values of the 6th item.If i use the "MoveDown"
again the original item moves down and displayed and the
operation happens properly.
This problem occurs only at first scroll of the list
box,ie .either up or down.
I tried reproducing the problem on a VB form but there
the operation happens fine.This is occuring only on Excel.
So I guess there is nothing wrong with the code and this
might be a problem with the display driver.
Any solutions or suggestions to this problem?
Thanks in Advance,
Sanjay
Sanjay
>
Dave Peterson
I added a couple of me.repaints and doevents and that didn't seem to help.
This is what I did that reproduced the problem:
Option Explicit
Private Sub CommandButton1_Click()
Dim myTemp As String
With Me.ListBox1
If .ListIndex = -1 _
Or .ListIndex = 0 Then
Beep
Exit Sub
End If
myTemp = .List(.ListIndex - 1)
.List(.ListIndex - 1) = .List(.ListIndex)
.List(.ListIndex) = myTemp
.ListIndex = .ListIndex - 1
End With
Me.Repaint
DoEvents
End Sub
Private Sub CommandButton2_Click()
Dim myTemp As String
With Me.ListBox1
If .ListIndex = -1 _
Or .ListIndex = .ListCount - 1 Then
Beep
Exit Sub
End If
myTemp = .List(.ListIndex + 1)
.List(.ListIndex + 1) = .List(.ListIndex)
.List(.ListIndex) = myTemp
.ListIndex = .ListIndex + 1
End With
Me.Repaint
DoEvents
End Sub
Private Sub ListBox1_Click()
MsgBox Me.ListBox1.Value
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 25
Me.ListBox1.AddItem "aaaa" & iCtr
Next iCtr
Me.CommandButton1.Caption = "Move Up"
Me.CommandButton2.Caption = "Move Down"
End Sub
But if I cleared out the list (I used a single column), the problem seemed to go
away.
I didn't see the problem when I did this:
Option Explicit
Private Sub CommandButton1_Click()
Dim myTemp As String
Dim myArr As Variant
Dim myIndex As Long
With Me.ListBox1
If .ListIndex = -1 _
Or .ListIndex = 0 Then
Beep
Exit Sub
End If
myIndex = .ListIndex
myTemp = .List(.ListIndex - 1)
myArr = Me.ListBox1.List
myArr(myIndex - 1, 0) = myArr(myIndex, 0)
myArr(myIndex, 0) = myTemp
.Clear
.List = myArr
.ListIndex = myIndex - 1
End With
End Sub
Private Sub CommandButton2_Click()
Dim myTemp As String
Dim myArr As Variant
Dim myIndex As Long
With Me.ListBox1
If .ListIndex = -1 _
Or .ListIndex = .ListCount - 1 Then
Beep
Exit Sub
End If
myIndex = .ListIndex
myTemp = .List(.ListIndex + 1)
myArr = Me.ListBox1.List
myArr(myIndex + 1, 0) = myArr(myIndex, 0)
myArr(myIndex, 0) = myTemp
.Clear
.List = myArr
.ListIndex = myIndex + 1
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 25
Me.ListBox1.AddItem "aaaa" & iCtr
Next iCtr
Me.CommandButton1.Caption = "Move Up"
Me.CommandButton2.Caption = "Move Down"
End Sub
--
Dave Peterson
ec3...@msn.com
Dave Peterson
me.listbox1.value showed me what was really there--not what was displayed.
--
Dave Peterson
ec3...@msn.com
Sanjay
Thanks a lot for your suggestion.I followed the
workaround suggested by you and implemented this in the
code.
Now it seems to work fine.
regards,
Sanjay
>.
>