Populating List box with tab/worksheet names
 

Is there a way to populate a list box embedded in a worksheet with the names
I've assigned to the worksheets/tabs? Thanks!

Populating List box with tab/worksheet names
 

Try this, when you activate the worksheet the names are populated:

Private Sub Worksheet_Activate()
Dim Sh As Worksheet
For Each Sh In Sheets
Me.ListBox1.AddItem (Sh.Name)
Next Sh
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26045

Populating List box with tab/worksheet names
 

Thanks, I will try that!

"The Code Cage Team" wrote:


Try this, when you activate the worksheet the names are populated:

Private Sub Worksheet_Activate()
Dim Sh As Worksheet
For Each Sh In Sheets
Me.ListBox1.AddItem (Sh.Name)
Next Sh
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26045

Populating List box with tab/worksheet names
 

Hit a snag. It's throwing me an error when I use "Me" and when I remove that
it gives me an "object required" error. here's the code:

Sub Auto_Open()

Dim Sh As Worksheet
For Each Sh In Sheets
Me.RegionSelect.AddItem (Sh.Name) ' RegionSelect is the name of the list
box embeded in the sheet
Next Sh
Application.ScreenUpdating = False
Application.Run "Format"
End Sub

"The Code Cage Team" wrote:


Try this, when you activate the worksheet the names are populated:

Private Sub Worksheet_Activate()
Dim Sh As Worksheet
For Each Sh In Sheets
Me.ListBox1.AddItem (Sh.Name)
Next Sh
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26045

Populating List box with tab/worksheet names
 

Sorry, just thought of one more related question. How would I handle the
need to populate the list box with only certain sheet names? Have the code
put the names in a cell range and then link the list box to just the part of
the range I needed? Thanks!

"The Code Cage Team" wrote:


Try this, when you activate the worksheet the names are populated:

Private Sub Worksheet_Activate()
Dim Sh As Worksheet
For Each Sh In Sheets
Me.ListBox1.AddItem (Sh.Name)
Next Sh
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26045

Populating List box with tab/worksheet names
 

Firstly the Me operator referes to the object and can only be used in
the object so if your sheet is called Sheet1 then you have to use the
code in sheet1 code module, here's how to use it in your method, change
activesheet to be the name of the sheet you are working with:

Sub Auto_Open()

Dim Sh As Worksheet
For Each Sh In Sheets
If Sh.name = "Sheet1" or Sh.name ="Mysheet" Then
else
Activesheet.RegionSelect.AddItem (Sh.Name) ' RegionSelect is the name
of the list
box embeded in the sheet
end if
Next Sh
Application.ScreenUpdating = False
Application.Run "Format"
End Sub

as for the rest of your question feel free to join our forum where you
can make a post and upload a workbook where we will be able to help you
further!


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26045