Putting Excel Worksheet Names in a Drop Down Box

[Jennpen1]

I have an Excel file with about 20 worksheets in it. Two of the worksheets are named first and last. What I want to do is pull all of the workbook names between first and last into a drop down box. I know this has to be a simple For Loop but I am struggling with pulling the names without activating any of the sheets. Please help!!

 

[xlbo]

Use a for...Next loop through the worksheets collection

dim sht as worksheet

For Each sht in thisworkbook.worksheets
  if sht.name <> "First" and sht.Name <> "Last" then
   DropdownBoxName.additem sht.name
  end if
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

This does not seem to work for a couple of reasons:

1--The worksheets first and last are not the first and last worksheets in the file. For example, there are 20 worksheets and first is the 3rd worksheet and last is the 15th. I want the names of worksheets 4-14 in a dropdown box. Due to other things going on in the file the location of first and last might change.

2--The If statment says only do if the sheet name is not first and last. None of the sheets are first AND last so it is pulling all of the sheet names into the dropdown box.

This is a great start for me, but is there any kind of between code? Like if sht is between Sheets("First) and Sheets("Last")??

 

[xlbo]

1 - nope - what you really need to do is to structure your workbook properly - you might also want to give us ALL relevant data (like sheets ("First" and "Last" are not first and last)

2 - really ? are you sure ? is it pulling sheets "first" and "last" in there ??

Your post asked for a routine to loop through sheets excluding sheets called "first" and "last" - that is what you have

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

Yes, it is pulling the sheets first and last into the dropdown box.

 

[GlennUK]

Hi JennPen1,

post your code please, in case you've made a spelling mistake.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Jennpen1]

Okay, it was not bringing in first and last. I just forgot to add the function that clears the box before I add more to it. (I was seeing the first and last from previous runs)

However, it is still not doing exactly what I need it to do. It is bringing in the sheets before and after "first" and "last" also and those are the sheets I do not want. I am playing around with a do until loop that starts on sheet "first" and runs until it gets to "last". No luck thus far, so help is welcome.

My current code is as follows:

Private Sub ComboBox1_Click()

Dim sht As Worksheet

ComboBox1.Clear

For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "First" And sht.Name <> "Last" Then
ComboBox1.AddItem sht.Name
End If
Next

End Sub

 

[SkipVought]

Jennpen1,

Please learn to state you requirements cleary, ciscisely and completely.

It SEEMS that what you have is a series for sheets, and SOMEWHERE in that physical series, is a sheet named "first" and subsequently a sheet named "last" that are not necessarily the first and last sheets respectively, but where "last" follows "first". And furthermore, you only want the sheets between "first" and "last" added to the box.

Is that the case?

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[GlennUK]

Try this:

Private Sub ComboBox1_Click()

Dim sht As Worksheet
Dim bFlagCopy as Boolean

ComboBox1.Clear

For Each sht In ThisWorkbook.Worksheets
  If sht.Name = "First" Then 
    bFlagCopy = True
  ElseIf sht.Name = "Last" Then
    bFlagCopy = False
  Else
    If bFlagCopy Then ComboBox1.AddItem sht.Name
  End If
Next

End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

"cleary, ciscisely "

clearly not correct although concise!

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[Jennpen1]

Glenn,

You are amazing. It works perfectly. Thanks for the help.

I apologize for not being clearer from the start.

Jenn

 

[GlennUK]

Geoff ( xlbo ) beat me to it on the first guess at code ( he types faster ), but it looks like I got there in the end.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.