Worksheet Selection
 

Is it possible to set up a proceedure that will take a list of all the
worksheets in a workbook and produce a list of these alphabetically. This
list then to be used in such a way as when the user selects a particular
sheet name they are taken straight to that sheet.

Thanks

Gaz

Worksheet Selection
 

Hi Gaz,

Sounds like you are wanting to use a UserForm. I'll assume this is correct
and you have these things installed on a UserForm:

Label, saying, "Choose Worksheets" (Label1)
ComboBox, will contain sheet names (ComboBox1)
CommandButton, saying, "OK" (CommandButton1)
CommandButton, saying, "Close" (CommandButton2)

Enter this code into your Userform ...


Option Explicit
Private Sub UserForm_Initialize()
Dim tmpWs As Worksheet, i#, n#, ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Set tmpWs = Worksheets.Add(befo=Sheets(1))
For i = 2 To Worksheets.Count
tmpWs.Range("A" & i - 1) = Worksheets(i).Name
Next
tmpWs.Range("A1:A" & i - 2).Sort Key1:=Range("A1"), Header:=xlGuess
For n = 1 To i - 2
Me.ComboBox1.AddItem tmpWs.Range("A" & n).Text
Next
Me.ComboBox1.ListIndex = 0
tmpWs.Delete
ws.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
On Error GoTo errHandle
Worksheets(Me.ComboBox1.Value).Activate
Unload Me
Exit Sub
errHandle:
MsgBox "An error has occured!"
Resume
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub


--
Regards,
Zack Barresse, aka firefytr

"Gazza" wrote in message
...
Is it possible to set up a proceedure that will take a list of all the
worksheets in a workbook and produce a list of these alphabetically. This
list then to be used in such a way as when the user selects a particular
sheet name they are taken straight to that sheet.

Thanks

Gaz

Worksheet Selection
 

or a modified dialog

Sub BrowseSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False


If ActiveWorkbook.ProtectStructur*e Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If


Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Ad*d


iBooks = 0


TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Coun*t
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks)*.Text = _
ActiveWorkbook.Worksheets(iBoo*ks).Name
TopPos = TopPos + 13
Next i


PrintDlg.Buttons.Left = 240


CurrentSheet.Activate


With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With


PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront


Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
'ActiveWorkbook.Worksheets(cb.*Caption).Select
MsgBox "Worksheet " & Worksheets(cb.Caption).Name & "
selected"
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If


Application.DisplayAlerts = False
PrintDlg.Delete


End Sub



--
HTH

Bob Phillips

"zackb" wrote in message
...
Hi Gaz,

Sounds like you are wanting to use a UserForm. I'll assume this is
correct
and you have these things installed on a UserForm:

Label, saying, "Choose Worksheets" (Label1)
ComboBox, will contain sheet names (ComboBox1)
CommandButton, saying, "OK" (CommandButton1)
CommandButton, saying, "Close" (CommandButton2)

Enter this code into your Userform ...


Option Explicit
Private Sub UserForm_Initialize()
Dim tmpWs As Worksheet, i#, n#, ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Set tmpWs = Worksheets.Add(befo=Sheets(1))
For i = 2 To Worksheets.Count
tmpWs.Range("A" & i - 1) = Worksheets(i).Name
Next
tmpWs.Range("A1:A" & i - 2).Sort Key1:=Range("A1"), Header:=xlGuess
For n = 1 To i - 2
Me.ComboBox1.AddItem tmpWs.Range("A" & n).Text
Next
Me.ComboBox1.ListIndex = 0
tmpWs.Delete
ws.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
On Error GoTo errHandle
Worksheets(Me.ComboBox1.Value).Activate
Unload Me
Exit Sub
errHandle:
MsgBox "An error has occured!"
Resume
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub


--
Regards,
Zack Barresse, aka firefytr

"Gazza" wrote in message
...
Is it possible to set up a proceedure that will take a list of all the
worksheets in a workbook and produce a list of these alphabetically.
This
list then to be used in such a way as when the user selects a particular
sheet name they are taken straight to that sheet.

Thanks

Gaz

Worksheet Selection
 

Bob,

The workbook I have has about 100 worksheets, the proceedure you have
written shows about 40 of these on screen but doesn't sort them
alphabetically to allow selection. I like the idea of creating the dialog
box using code but I really need a combo box to list the sheets in
alphabetical order ( I have valid reason why I dont want to sort the sheets
alphabetically in the workbook)

Thanks anyway

Gaz
"Bob Phillips" wrote in message
...
or a modified dialog

Sub BrowseSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False


If ActiveWorkbook.ProtectStructur*e Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If


Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Ad*d


iBooks = 0


TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Coun*t
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks)*.Text = _
ActiveWorkbook.Worksheets(iBoo*ks).Name
TopPos = TopPos + 13
Next i


PrintDlg.Buttons.Left = 240


CurrentSheet.Activate


With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With


PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront


Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
'ActiveWorkbook.Worksheets(cb.*Caption).Select
MsgBox "Worksheet " & Worksheets(cb.Caption).Name & "
selected"
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If


Application.DisplayAlerts = False
PrintDlg.Delete


End Sub



--
HTH

Bob Phillips

"zackb" wrote in message
...
Hi Gaz,

Sounds like you are wanting to use a UserForm. I'll assume this is
correct
and you have these things installed on a UserForm:

Label, saying, "Choose Worksheets" (Label1)
ComboBox, will contain sheet names (ComboBox1)
CommandButton, saying, "OK" (CommandButton1)
CommandButton, saying, "Close" (CommandButton2)

Enter this code into your Userform ...


Option Explicit
Private Sub UserForm_Initialize()
Dim tmpWs As Worksheet, i#, n#, ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Set tmpWs = Worksheets.Add(befo=Sheets(1))
For i = 2 To Worksheets.Count
tmpWs.Range("A" & i - 1) = Worksheets(i).Name
Next
tmpWs.Range("A1:A" & i - 2).Sort Key1:=Range("A1"), Header:=xlGuess
For n = 1 To i - 2
Me.ComboBox1.AddItem tmpWs.Range("A" & n).Text
Next
Me.ComboBox1.ListIndex = 0
tmpWs.Delete
ws.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
On Error GoTo errHandle
Worksheets(Me.ComboBox1.Value).Activate
Unload Me
Exit Sub
errHandle:
MsgBox "An error has occured!"
Resume
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub


--
Regards,
Zack Barresse, aka firefytr

"Gazza" wrote in message
...
Is it possible to set up a proceedure that will take a list of all the
worksheets in a workbook and produce a list of these alphabetically.
This
list then to be used in such a way as when the user selects a
particular
sheet name they are taken straight to that sheet.

Thanks

Gaz