Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent worksheet selection | Excel Discussion (Misc queries) | |||
Lock worksheet, based on dropdown selection prior worksheet | New Users to Excel | |||
Worksheet, selection macro | Excel Worksheet Functions | |||
Correlate selection row with worksheet row | Excel Programming | |||
Worksheet Selection Change | Excel Programming |