As long as you can see the order of the items in the listbox, the reorder
code should work.
If you hide your userform, then run the reorder in the original module, then
unload the form, it should work fine (preface the references to the listbox
with the userform name. ).
--
Regards,
Tom Ogilvy
Stuart wrote in message
...
I hoped that the moveup/down button code would order
the listbox, then user would click Done. Code would
revert to the module, where the actual sheet tab changes
would be handled.
All that would be seen in the form, would be the apparent
change in the sequence of sheet names .
Many thanks for the spinbutton routines.
Regards.
"Tom Ogilvy" wrote in message
...
I wouldn't move the sheets on each change. Have the user make their
selections, then order the sheets based on the order in the listbox.
(or
call the code in CommandButton1_click() whenever you want to reorder the
tab
order).
Private Sub CommandButton1_Click()
With ListBox1
For i = .ListCount - 1 To 1 Step -1
Worksheets(.List(i)).Move After:=Worksheets(.List(0))
Next
End With
End Sub
Private Sub SpinButton1_SpinUp()
If ListBox1.ListIndex < -1 Then
i = ListBox1.ListIndex
If i = 0 Then Exit Sub
s = ListBox1.List(i)
ListBox1.RemoveItem i
ListBox1.AddItem s, i - 1
ListBox1.ListIndex = i - 1
SpinButton1.Value = 0
End If
End Sub
Private Sub SpinButton1_SpinDown()
If ListBox1.ListIndex < -1 Then
i = ListBox1.ListIndex
If i = ListBox1.ListCount - 1 Then Exit Sub
s = ListBox1.List(i)
ListBox1.RemoveItem i
ListBox1.AddItem s, i + 1
ListBox1.ListIndex = i + 1
SpinButton1.Value = 0
End If
End Sub
Private Sub UserForm_Initialize()
For Each sh In Worksheets
ListBox1.AddItem sh.Name
Next
End Sub
--
Regards,
Tom Ogilvy
Stuart wrote in message
...
Thanks for the suggestion. Have amended the spinbox to
MoveUp and MoveDown buttons. Can get sheetnames
into the listbox.
When back in the module, how do I relate the position in
the listbox to the sheet's tab index (I presume that is the route) ?
Regards.
"Tom Ogilvy" wrote in message
...
Selecting an item and using a spinbutton?
just remove the item and add it back at the appropriate location
(one
position up or down).
--
Regards,
Tom Ogilvy
Stuart wrote in message
...
I'm sure I've seen somewhere (but cannot locate now)
code that will allow the user to change the order of
items in a listbox.
I want to use this code to allow a user to change the
tab order of worksheets, and thought I could populate
the listbox with the sheet names.
Can anyone help, please?
Regards.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.586 / Virus Database: 371 - Release Date: 12/02/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.586 / Virus Database: 371 - Release Date: 12/02/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.586 / Virus Database: 371 - Release Date: 12/02/2004