Combo Box to list sheet names 1

[ztm]

I'm putting a user form in an Excel 2000 spreadsheet that has has several worksheets. On the form I have two comboboxes. I need the first combobox to list the names of all the worksheets, i.e. "sheet1", "sheet2", etc. Then, I need the other combobox to list the contents of a range of cells located on the worksheet selected in the first combo box. Thanks in advance for any help or suggestions.

 

[SkipVought]

Hi,
In Excel are various Collections...
Worksheets Collection for instance.

So here's what you can do...
[cells]
Private Sub UserForm_Activate()
'this adds the names of the worksheets
For Each Worksheet In Worksheets
UserForm1.ComboBox1.AddItem Worksheet.Name
Next
End Sub
[/code]
I use the same range name on each sheet to define the dropdown list for that sheet -- called "Whatever"...

Private Sub ComboBox1_Change()
'this removes any former list
    For i = 1 To UserForm1.ComboBox2.ListCount
        If UserForm1.ComboBox2.ListIndex = -1 Then
            UserForm1.ComboBox2.ListIndex = _
                    UserForm1.ComboBox2.ListCount - 1
        End If
        UserForm1.ComboBox2.RemoveItem (UserForm1.ComboBox2.ListIndex)
    Next
'this adds the list on the selected sheet
    For Each cell In Sheets(UserForm1.ComboBox1.Text).Range("Whatever")
        UserForm1.ComboBox2.AddItem cell.Value
    Next
End Sub

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[ztm]

Skip,
Thanks very much!! I was able to paste your code almost exactly as you posted it. All I had to do was define the range and add one line...
Sheets(UserForm1.ComboBox1.Text).Activate - just before the last For..Next loop. I was getting an application error before that. Thanks again.

 

[DaleWatson123321]

ztm,

Your "thanks" is appreciated by Skip, I'm sure, but because your profile shows ZERO "STARS" awarded, I have to expect that you are not yet aware of the practice.

At Tek-Tips, there is a longstanding practice of showing "full gratitude" for the help offered freely, by taking the time to issue a "STAR".

The issuing of a STAR is done by simply clicking on the "click here to mark this post as a helpful or expert post!" which is located at the bottom-left-corner of the contributor's screen.

These "STARS" properly recognize the contributor's help, but ALSO serve two other purposes: 1) to let other would-be contributors know (at a glance) that the problem has been resolved, and 2) it serves as a "beacon" for other Tek-Tips "browsers" who are on the lookout for solutions for the same or similar problems, or are just wanting to improve their general skills.

"ztm", because you don't have this posting "marked", you might not see this message, so I'll take the liberty of issuing Skip a STAR on your behalf.

For other readers, PLEASE don't consider my message a "tip" - i.e. DON'T give me a STAR - as I want to "earn" them the normal way. (I feel the need to mention this, as I did receive a STAR in the past for such a reminder).

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ztm]

Dear Dale, Skip, and other members of the forum. No offense intended. I am very grateful for the help I've received and am quite impressed with the generosity and knowledge that members have shown. You're right, I was unaware of the "Star treatment". It won't happen again.