Show Only Select Worksheets In Combo Box 2

[ballbearing]

In an Excel program I'm working on, the user can "archive" certain employee's data. The archiving process creates a worksheet named, such as, "John Smith_Archive". Getting a workbook's worksheet list is not a problem, but I can't figure out how to show ONLY the "Employee_Archive" sheets in a combo box for a user to select from to retrieve.

Ideas?

Thanks

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[Molby]

Are you using code to get the list? Can you tell us how you have populated the combo box?

 

[ballbearing]

Yes, I wanted to use code for this. I figure if I can get the "Archive" worksheet names listed on a different worksheet, the combo box can pick it up from there.

Make sense? Hope so.

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[Molby]

Try this

Private Sub ComboBox1_DropButtonClick()
Dim mysheet As Worksheet
For Each mysheet In Worksheets
  If InStr(mysheet.Name, "archive") Then
    ComboBox1.AddItem mysheet.Name
  Else
  End If
Next
End Sub

 

[ballbearing]

Would work fine except that each archive has it's own unique sheet name (i.e. John Smith_Archive, Sue Behm_Archive and so on) Is there any way to use wildcards (*) or something in code? ("*" & "_Archive") doesn't work.

Was thinking what I might have to do is have the code place each newly added sheet name in a column and have it be a hyperlink. That way I can populate the combo box as well. Might be a better way...what do you think?

Gawd I love this Access stuff..too bad I don't know what I'm doing.

 

[Molby]

Actually there was a problem with the previous code in that it wold keep adding the names if the user selected the box more than once, use this instead.

Private Sub ComboBox1_DropButtonClick()
Dim mysheet As Worksheet
a = ComboBox1.ListCount
If a = 0 Then
  For Each mysheet In Worksheets
    If InStr(mysheet.Name, "Ian") Then
      ComboBox1.AddItem mysheet.Name
    Else
    End If
  Next
Else
End
End If
End Sub

 

[Molby]

That's what the INSTR is for, it looks for the word archive in the whole sheet name. I've tested this out and it works fine.

 

[Bowers74]

Assuming that the ComboBox is located on a UserForm:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
ComboBox1.Clear
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "*_Archive" Then
        ComboBox1.AddItem ws.Name
    End If
Next ws
End Sub

Hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[ballbearing]

Sorry about the delay in getting back with both of you. Had a meeting.

Thanks to the both of you, Molby and Bowers, things are looking brighter now!
Thanks again for the help.

Gawd I love this Access stuff..too bad I don't know what I'm doing.