Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prioritised listbox
hope this question isn't too simple...
i have three objectives... 1) i want to populate a list box with values (coming from a userform) in a specific order, am i right in thinking that if i use .additem it will keep them in the order they're added (ie, it won't sort them alphanumerically) 2) i want to be able to prioritise that list if something is added in the wrong place / order (like in the References dialog box in the VBA window - using up/down spin controls?) 3) i want to use the newly ordered list, by assigning each of the list values to individual flow diagram boxes, then link the with connecting lines my main problem is #2 (moving values around in a list box), but if anyone can help with any / all parts that would be great. Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prioritised listbox
Tim
1) You are correct. What ever order you AddItem them, they will stay. 2) See the code for CmdUp and CmdDown below. 3) See the code for CmdDiagram below. I have a userform with a listbox and three command buttons. CmdUp and CmdDown reorder the listbox and CmdDiagram creates a flow chart with connectors on Sheet1. Here's the contents of the code module behind the userform. I've put comments in the code to help you follow it, but be sure to post back if you have questions. Option Explicit Private Sub cmdDiagram_Click() Dim i As Long Dim aShp() As Shape Dim Conn As Shape Dim DelShp As Shape 'clear the shapes on the sheet for new entry For Each DelShp In Sheet1.Shapes DelShp.Delete Next DelShp 'create an array to held the shapes for later 'connecting ReDim aShp(0 To Me.ListBox1.ListCount - 1) 'Loop through the listbox and add shapes to Sheet1 For i = 0 To Me.ListBox1.ListCount - 1 Set aShp(i) = Sheet1.Shapes.AddShape(msoShapeRectangle, 189.75, (i + 1) * 64.5, 72#, 45#) 'Insert the listbox entry as text into the shape aShp(i).TextFrame.Characters.Text = Me.ListBox1.List(i) Next i 'Loop through the shapes and connect them For i = LBound(aShp) To UBound(aShp) If i < UBound(aShp) Then 'it doesn't matter where you create the connector because the 'BeginConnect and EndConnect properties are set later Set Conn = Sheet1.Shapes.AddConnector(msoConnectorStraight, 1, 1, 1, 1) 'The connecter is set to arbitrary connections (1) and the 'RerouteConnections method is used to adjust to the 'shortest path Conn.ConnectorFormat.BeginConnect aShp(i), 1 Conn.ConnectorFormat.EndConnect aShp(i + 1), 1 Conn.RerouteConnections End If Next i End Sub Private Sub CmdDown_Click() Dim LbEnt() As String Dim CurrEnt As Long Dim i As Long Dim Temp As String 'Make sure an item that can be moved is selected If Me.ListBox1.ListIndex = -1 Then MsgBox "you must select an item" ElseIf Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 Then MsgBox "you can't move the last item down" Else 'create an array to hold the contents of the listbox ReDim LbEnt(0 To Me.ListBox1.ListCount - 1) 'Remember what's selected CurrEnt = Me.ListBox1.ListIndex 'Fill the array For i = LBound(LbEnt) To UBound(LbEnt) LbEnt(i) = Me.ListBox1.List(i) Next i 'reorder the items in the array Temp = LbEnt(CurrEnt + 1) LbEnt(CurrEnt + 1) = LbEnt(CurrEnt) LbEnt(CurrEnt) = Temp Me.ListBox1.Clear 'Put the array back in the listbox For i = LBound(LbEnt) To UBound(LbEnt) Me.ListBox1.AddItem LbEnt(i) Next i 'Reselect the item Me.ListBox1.ListIndex = CurrEnt + 1 End If End Sub Private Sub CmdUp_Click() Dim LbEnt() As String Dim CurrEnt As Long Dim i As Long Dim Temp As String 'Basically the same comments as CmdDown If Me.ListBox1.ListIndex = -1 Then MsgBox "you must select an item" ElseIf Me.ListBox1.ListIndex = 0 Then MsgBox "you can't move the first item up" Else ReDim LbEnt(0 To Me.ListBox1.ListCount - 1) CurrEnt = Me.ListBox1.ListIndex For i = LBound(LbEnt) To UBound(LbEnt) LbEnt(i) = Me.ListBox1.List(i) Next i Temp = LbEnt(CurrEnt - 1) LbEnt(CurrEnt - 1) = LbEnt(CurrEnt) LbEnt(CurrEnt) = Temp Me.ListBox1.Clear For i = LBound(LbEnt) To UBound(LbEnt) Me.ListBox1.AddItem LbEnt(i) Next i Me.ListBox1.ListIndex = CurrEnt - 1 End If End Sub Private Sub UserForm_Initialize() Me.ListBox1.AddItem "3 My first entry" Me.ListBox1.AddItem "1 My second entry" Me.ListBox1.AddItem "2 My last entry" End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Tim Marsh" wrote in message ... hope this question isn't too simple... i have three objectives... 1) i want to populate a list box with values (coming from a userform) in a specific order, am i right in thinking that if i use .additem it will keep them in the order they're added (ie, it won't sort them alphanumerically) 2) i want to be able to prioritise that list if something is added in the wrong place / order (like in the References dialog box in the VBA window - using up/down spin controls?) 3) i want to use the newly ordered list, by assigning each of the list values to individual flow diagram boxes, then link the with connecting lines my main problem is #2 (moving values around in a list box), but if anyone can help with any / all parts that would be great. Many thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prioritised listbox
you're a star... I'll try it tonight - thank you!
"Dick Kusleika" wrote in message ... Tim 1) You are correct. What ever order you AddItem them, they will stay. 2) See the code for CmdUp and CmdDown below. 3) See the code for CmdDiagram below. I have a userform with a listbox and three command buttons. CmdUp and CmdDown reorder the listbox and CmdDiagram creates a flow chart with connectors on Sheet1. Here's the contents of the code module behind the userform. I've put comments in the code to help you follow it, but be sure to post back if you have questions. Option Explicit Private Sub cmdDiagram_Click() Dim i As Long Dim aShp() As Shape Dim Conn As Shape Dim DelShp As Shape 'clear the shapes on the sheet for new entry For Each DelShp In Sheet1.Shapes DelShp.Delete Next DelShp 'create an array to held the shapes for later 'connecting ReDim aShp(0 To Me.ListBox1.ListCount - 1) 'Loop through the listbox and add shapes to Sheet1 For i = 0 To Me.ListBox1.ListCount - 1 Set aShp(i) = Sheet1.Shapes.AddShape(msoShapeRectangle, 189.75, (i + 1) * 64.5, 72#, 45#) 'Insert the listbox entry as text into the shape aShp(i).TextFrame.Characters.Text = Me.ListBox1.List(i) Next i 'Loop through the shapes and connect them For i = LBound(aShp) To UBound(aShp) If i < UBound(aShp) Then 'it doesn't matter where you create the connector because the 'BeginConnect and EndConnect properties are set later Set Conn = Sheet1.Shapes.AddConnector(msoConnectorStraight, 1, 1, 1, 1) 'The connecter is set to arbitrary connections (1) and the 'RerouteConnections method is used to adjust to the 'shortest path Conn.ConnectorFormat.BeginConnect aShp(i), 1 Conn.ConnectorFormat.EndConnect aShp(i + 1), 1 Conn.RerouteConnections End If Next i End Sub Private Sub CmdDown_Click() Dim LbEnt() As String Dim CurrEnt As Long Dim i As Long Dim Temp As String 'Make sure an item that can be moved is selected If Me.ListBox1.ListIndex = -1 Then MsgBox "you must select an item" ElseIf Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 Then MsgBox "you can't move the last item down" Else 'create an array to hold the contents of the listbox ReDim LbEnt(0 To Me.ListBox1.ListCount - 1) 'Remember what's selected CurrEnt = Me.ListBox1.ListIndex 'Fill the array For i = LBound(LbEnt) To UBound(LbEnt) LbEnt(i) = Me.ListBox1.List(i) Next i 'reorder the items in the array Temp = LbEnt(CurrEnt + 1) LbEnt(CurrEnt + 1) = LbEnt(CurrEnt) LbEnt(CurrEnt) = Temp Me.ListBox1.Clear 'Put the array back in the listbox For i = LBound(LbEnt) To UBound(LbEnt) Me.ListBox1.AddItem LbEnt(i) Next i 'Reselect the item Me.ListBox1.ListIndex = CurrEnt + 1 End If End Sub Private Sub CmdUp_Click() Dim LbEnt() As String Dim CurrEnt As Long Dim i As Long Dim Temp As String 'Basically the same comments as CmdDown If Me.ListBox1.ListIndex = -1 Then MsgBox "you must select an item" ElseIf Me.ListBox1.ListIndex = 0 Then MsgBox "you can't move the first item up" Else ReDim LbEnt(0 To Me.ListBox1.ListCount - 1) CurrEnt = Me.ListBox1.ListIndex For i = LBound(LbEnt) To UBound(LbEnt) LbEnt(i) = Me.ListBox1.List(i) Next i Temp = LbEnt(CurrEnt - 1) LbEnt(CurrEnt - 1) = LbEnt(CurrEnt) LbEnt(CurrEnt) = Temp Me.ListBox1.Clear For i = LBound(LbEnt) To UBound(LbEnt) Me.ListBox1.AddItem LbEnt(i) Next i Me.ListBox1.ListIndex = CurrEnt - 1 End If End Sub Private Sub UserForm_Initialize() Me.ListBox1.AddItem "3 My first entry" Me.ListBox1.AddItem "1 My second entry" Me.ListBox1.AddItem "2 My last entry" End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Tim Marsh" wrote in message ... hope this question isn't too simple... i have three objectives... 1) i want to populate a list box with values (coming from a userform) in a specific order, am i right in thinking that if i use .additem it will keep them in the order they're added (ie, it won't sort them alphanumerically) 2) i want to be able to prioritise that list if something is added in the wrong place / order (like in the References dialog box in the VBA window - using up/down spin controls?) 3) i want to use the newly ordered list, by assigning each of the list values to individual flow diagram boxes, then link the with connecting lines my main problem is #2 (moving values around in a list box), but if anyone can help with any / all parts that would be great. Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox 2 takes the value of Listbox 1 | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |