Many thanks.
I know I'll use it, but I've started to follow Tom's suggestion,
so will also follow that through (for my benefit).
Regards.
"Bob Phillips" wrote in message
...
Stuart,
Nor answering the question , but by taborder of worksheets, I assume you
mean order. On this basis, I present here a utility that allows you to
specify the sheet order, and it then orders them for you.
'---------------------------------------------------------------------
Sub dlgSheetSort()
'---------------------------------------------------------------------
'Function: Custom sheet order dialog
'Author: Originally written by John Walkenbach
' Adapted and enhanced by Bob Phillips
'Synopsis: Builds a print dialog with a list of worksheets with
' current sheet order.
' The dialog is then displayed to allow setting of
' custom sheet order.
' Igf OK, order macro called.
' Finaly dialog is cleared down
'---------------------------------------------------------------------
Const sTitle As String = "Custom Sheet Order"
Const sMsgTitle As String = "Custom Order"
Const sID As String = "___CustOrder"
Dim PrintDlg As DialogSheet
Dim oThis As Workbook
Dim CurrentSheet As Worksheet
Dim oCtl As EditBox
Dim nBinary As Long
Dim i As Long
Dim j As Long
Dim iTopPos As Long
Dim iItems As Long
Dim aryOrder()
Application.ScreenUpdating = False
Set oThis = ActiveWorkbook
If oThis.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical, sMsgTitle
Exit Sub
End If
Set CurrentSheet = ActiveSheet
Set PrintDlg = oThis.DialogSheets.Add
With PrintDlg
.Name = sID
.Visible = xlSheetHidden
iItems = 0
iTopPos = 40
For i = 1 To oThis.Sheets.Count
'skip hidden sheets
If oThis.Sheets(i).Visible < xlSheetHidden Then
If oThis.Sheets(i).Name < sID Then
iItems = iItems + 1
.Labels.Add 78, iTopPos, 150, 16.5
.EditBoxes.Add 200, iTopPos, 24, 16.5
.EditBoxes(iItems).Caption = iItems
.Labels(iItems).Text = _
oThis.Sheets(i).Name
iTopPos = iTopPos + 13
End If
End If
Next i
.Buttons.Left = 240
With .DialogFrame
.Height = Application.Max(68, .Top + iTopPos - 34)
.Width = 230
.Caption = sTitle
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
.Buttons("Button 3").OnAction = "CancelButton"
Application.ScreenUpdating = True
Do
If .Show Then
fCancel = False
nBinary = 0
For Each oCtl In .EditBoxes
If oCtl.Caption < "" Then
nBinary = nBinary + 2 ^ (oCtl.Caption - 1)
End If
Next oCtl
If nBinary < 2 ^ iItems - 1 Then
MsgBox "invalid"
End If
End If
Loop Until nBinary = 2 ^ iItems - 1 Or fCancel
'If everything OK and not cancel
If Not fCancel Then
ReDim aryOrder(1 To .EditBoxes.Count)
For i = .EditBoxes.Count To 1 Step -1
For j = 1 To .EditBoxes.Count
If i = .EditBoxes(j).Caption Then
aryOrder(i) = .Labels(j).Text
Exit For
End If
Next j
Next i
Ordersheets aryOrder
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub
Private Sub Ordersheets(Order)
Dim i As Long
For i = UBound(Order) To LBound(Order) Step -1
Sheets(Order(i)).Move befo=Sheets(1)
Next i
End Sub
Private Sub CancelButton()
fCancel = True
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"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