move listbox values up and down in userform

[gwh]

Hi everyone,

I've put together a multipage userform and on one of the pages I have two textboxes and one list box. The user enters milestone information into one of the textboxes and a date into the second box. Once data is entered into both of the textboxes, I have an "add" button to insert the information into a 2 column listbox. The user continues to add as many date/milestone pairs as needed.

I also have a delete button to delete the entries from the listbox and another button to edit an entry that was put in the listbox.

The problem at the moment is with the edit process. It works okay, ie. when pressed the row in the list box that's clicked on is transferred from the listbox back into the two textboxes so the entries can be edited, then the user can click "add" again to reinsert the information back into the listbox. The only thing is that when they re-add the info after editing, it doesn't appear in the right order, ie. it appears at the end of the list in the listbox. Since they need to exist in the listbox in date order, is there a way to either have the entry jump back to the original location it occupied before it was edited or else have "up" and "down" buttons that will move the entry into its correct place?

Below is the code as it is now.

I'd really be grateful if someone could help me out here.


Private Sub cmdAddTiming_Click()
With lstTiming_Milestones
.AddItem txtTiming_Milestone.Value
.Column(1, .ListCount - 1) = txtTiming_Date.Value
End With
txtTiming_Milestone.Value = ""
txtTiming_Date.Value = ""
End Sub

Private Sub cmdDeleteTiming_Click()
With lstTiming_Milestones
.RemoveItem (.ListIndex)
End With
End Sub

Private Sub cmdEditTiming_Click()
With lstTiming_Milestones
txtTiming_Milestone.Value = .Column(0, .ListIndex)
txtTiming_Date.Value = .Column(1, .ListIndex)
.RemoveItem (.ListIndex)
End With
End Sub


Dim i As Long
Dim mtable As Table
Dim mrow As Row
Set mtable = _
ActiveDocument.Bookmarks("Timing_Milestones").Range.Tables(1)
With lstTiming_Milestones
For i = 1 To .ListCount
Set mrow = mtable.Rows.Add
mrow.Range.Font.Bold = False
mrow.Range.Font.Italic = False
mrow.Range.Font.Color = RGB(0, 0, 0)
mrow.Cells(1).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
mrow.Cells(1).Range.Text = .Column(0, i - 1)
mrow.Cells(2).Range.Text = .Column(1, i - 1)
Next i
End With

[fumei]

"The only thing is that when they re-add the info after editing, it doesn't appear in the right order, ie. it appears at the end of the list in the listbox. "

Yes, of course it does. You are removing the item with your .RemoveItem.

It is gone. So adding the edited content does just that. It adds it. And it adds it at the end. You do not want to add it, you want to change it, yes?

So, have a Public array (in a standard module, not the userform module), Redim that array with the items of the listbox, get the ListIndex of the selected item. Then make the array value the new value (the edited value in the textbox), clear the Listbox, and repopulate with the new array.

There is a way to do this. Demo attached. Here is the core code.Private Sub cmdChange_Click()
Dim var
On Error Resume Next
For var = 0 To ListBox1.ListCount
ReDim Preserve MyList(var)
MyList(var) = ListBox1.List(var)
Next
MyList(mySelected) = TextBox1.Text
ListBox1.Clear
ListBox1.List = MyList()
End Sub

The variable MySelected is the ListIndex number of the selected item in the ListBox...say 1. That is, they selected the second item.

The text value is dumped into the textbox with the ListBox_Change event.

They edit it in the textbox and click the Change (NOT Add!) button.

The Change button builds the array (MyList) of the existing items, CHANGES the array item value to the textbox text, clears the Listbox and repopulates with the new array.

In the demo attached, the Listbox is populated originally with:

Stuff_1
Bob
George

Say you select "Bob". The Listbox Change event dumps "Bob" into the textbox. It sets the variable MySelected = 1 (i.e. the ListIndex of "Bob")

In the textbox you change "Bob" to "Bobby", and click Change.

The Change_Click event:

1. builds the array (MyList) of the existing Listbox items - i.e

MyList(0) = "Stuff_1"
MyList(1) = "Bob"
MyList(2) = "George"

2. CHANGES the array item value by MySelected (which was 1..."Bob").

MyList(1) = Textbox1.Text

So now MyList(1) - i.e "Bob" - becomes MyList(1) = "Bobby"

3. Clear the entire Listbox (.Clear)

4. Populate the Listbox with the array.

Listbox now shows:

Stuff_1
Bobby
George

AFAIK, there is no other way to do it. You can not "edit" a Listbox item directly.

[fumei]

BTW: I would strongly suggest having some sort of ReSet procedure. This would reset the listbox to its original values. You could also use it as the first time listing.

[gwh]

Thanks for the detailed reply. Okay so here is part of the code in the userform:

Option Explicit
Dim bAllowValidate As Boolean
Public mySelected As Long

Private Sub UserForm_Initialize()

MultiPage1.Value = 0

End Sub


Private Sub cmdChange_Click()
Dim var
On Error Resume Next
For var = 0 To lstTiming_Milestones.ListCount
ReDim Preserve MyList(var)
MyList(var) = lstTiming_Milestones.List(var)
Next
MyList(mySelected) = txtTiming_Milestone.Text
MyList(mySelected) = txtTiming_Date.Text 'extra line to accommodate 2nd text box
lstTiming_Milestones.Clear
lstTiming_Milestones.List = MyList()
End Sub


Private Sub lstTiming_Milestones_Change()
txtTiming_Milestone.Text = lstTiming_Milestones.Text
txtTiming_Date.Text = lstTiming_Milestones.Text 'extra line to accommodate 2nd text box
mySelected = lstTiming_Milestones.ListIndex
End Sub


Private Sub cmdAddTiming_Click()
With lstTiming_Milestones
.AddItem txtTiming_Milestone.Value
.Column(1, .ListCount - 1) = txtTiming_Date.Value
End With
txtTiming_Milestone.Value = ""
txtTiming_Date.Value = ""
End Sub

Private Sub cmdDeleteTiming_Click()
With lstTiming_Milestones
.RemoveItem (.ListIndex)
End With
End Sub

Note the two comments in the code where I say I put in the extra lines to accommodate the two text boxes instead of the one in your code.

When I test, I enter text into the two boxes called txtTiming_Milestone and txtTiming_Date. When I press "add" the data gets inserted into the two columns of the listbox. When I click on the row in the listbox, the values get inserted back into the two textboxes as they should and I can edit them, but when I click change the edited date gets inserted in the first column of the listbox (which is where the milestone should go) and the milestone disappears altogether.

I'm struggling a bit to understand how to adapt your code - can you explain further what might be going on?


BTW: I would strongly suggest having some sort of ReSet procedure. This would reset the listbox to its original values. You could also use it as the first time listing.


Also I don't quite understand the purpose of the reset procedure. Do you mean that if someone clicked on an entry to edit it, and then changed their mind, they could click reset and then it would put the entry back into the listbox? If so, can you tell me how this would work code-wise?

[gwh]

Also, I assume that I no longer need the edit button in my original code - is this right?

[gwh]

Can anyone offer any further help to solve the problems here? I'd really appreciate it as I'm running out of time.