Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
Created list of worksheet names but need list to be hyperlinked to | Excel Programming | |||
Populating worksheet via a drop down list ! | Excel Worksheet Functions | |||
Populating Userform ComboBox with worksheet names in XL2003 | Excel Programming |