ListBox Help

SandMan

New Member
Joined
Sep 2, 2002
Messages
8
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!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

Active Member
Joined
Feb 25, 2002
Messages
257
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

New Member
Joined
Sep 2, 2002
Messages
8
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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

New Member
Joined
Sep 2, 2002
Messages
8
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Forum statistics

Threads
1,143,709
Messages
5,720,390
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top