ListBox Help

[SandMan]

Is it possible to create a ListBox with all visible sheets listed(either on a sheet itself or in a UserForm) and then be able to bring up a sheet by selecting it from the ListBox?? I have been wracking my brain for 2 weeks trying to do this.....Please Help!!

 

[stevebausch]

On 2002-09-05 07:19, SandMan wrote:
Is it possible to create a ListBox with all visible sheets listed(either on a sheet itself or in a UserForm) and then be able to bring up a sheet by selecting it from the ListBox?? I have been wracking my brain for 2 weeks trying to do this.....Please Help!!

Off the top of my head (at break).
For TheLoop = 1 to ThisWorkbook.Sheets.Count
AddItem Sheets(TheLoop).Name
Next

Remember that the index will be off, so I wouldn't trust the ListIndex to find it's way back into the Sheets correctly.

Go with the List(ListIndex) string value, when you want to do Sheets(string value).activate

 

[philR]

OK, a little complicated to explain, but here goes:

First, I have created a userform called "usfOne". On it are a listbox called "lstSheets" and a command button called "cmdGo".

Next, I have created an ordinary module, and put the following code in it:

Sub AddSheetsToList()
usfOne.lstSheets.Clear
For Each sheet In ActiveWorkbook.Sheets
usfOne.lstSheets.AddItem (sheet.Name)
Next
usfOne.Show
End Sub

Finally, you need this code in the click event for the "cmdGo" button:

Private Sub cmdGo_Click()
Sheets(usfOne.lstSheets.Value).Activate
usfOne.Hide
End Sub

When you run the macro "AddSheetsToList" it will put all the sheets in the active workbook into the list box. When you select one of them and click the "cmdGo" button, it will activate the sheet you have selected.
You could do away with the button and make it a double click on the sheet you want, or numerous other options. Note: this will probably fall over if you have hidden sheets - you will need additional code to unhide them first.
Hope this helps!

Phil.

 

[SandMan]

Thank you for the info (and I'm sorry if I got you workin' on your break). I will try this and cross my fingers that it works.

 

[Joe Was]

The macros below will return the sheet names by code. You can use the return list as the data for a dropdown in cell list or a control. I did not code the select sheet code yet. JSW

Sub AllSheets1atATime()
'Get a sheet name.
For Each ws In Worksheets
'Display the found sheet name.
MsgBox ws.Name
Next ws
'Go back and get the next sheet name.
End Sub

Sub AllSheets()
Dim myList As String
'Get all sheets name.
For Each ws In Worksheets
'Store one sheet name after another!
myList = myList + ws.Name
'Add a comma and space between each stored name.
myList = myList + ", "
Next ws
'Display all the found sheet names.
MsgBox myList
End Sub

 

[SandMan]

I thank everyone for all their help...I decided to go with the UserForm code, create a shortcut key and it worked like a charm. I think my problem was I kept mixing up where I was putting the code (sometimes in a module for the list box and sometimes in a module for the UserForm) and I could only get half the Macro to work right!! All of your help was invaluable, thank you!

 

[Joe Was]

The Sub below builds an InCellDropDown List in "B5" on Sheet1. The event code will select the sheet from the DropDown List. JSW

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mySheet As String
Dim myPage

'This go's in the Sheet1 Module.

If Worksheets("Sheet1").Range("B5").Value = "" Then End
If Worksheets("Sheet1").Range("B5") = Target Then
Worksheets("Sheet1").Select
mySheet = Worksheets("Sheet1").Range("B5").Value
Sheets(mySheet).Select
myPage = ActiveSheet.Name
Worksheets("Sheet1").Range("B5").Value = ""
Sheets(myPage).Select
End If
End Sub

Sub BuildSListDD()
Dim myList As String

'This go's in a module.

For Each ws In Worksheets
myList = myList + ws.Name
myList = myList + ","
Next ws
With Range("B5").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, myList
.InCellDropdown = True
End With
Worksheets("Sheet1").Range("B5").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub