• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

limiting listbox results

kds14589

New Member
i have a userform with a listbox of worksheets from code thanks to 'NoSparks' @ Mr Excel that limits the worksheets so the Active one isn't listed.


>>> use code - tags <<<
Code:
Private Sub UserForm_Initialize()
''''NoSparks @ Mr Excel 3.6.21
Dim sh As Worksheet, shtnames As String
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
shtnames = shtnames & "|" & sh.Name
End If
Next sh
ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub

I've copied this userform so I can just have specific worksheets {*lists*] listed, i tried to modify the code to do this with no luck. My last attempt was


Code:
Dim sh As Worksheet, shtnames As String
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "*LISTS" Then
shtnames = shtnames & "|" & sh.Name
End If
Next sh
ListBox1.List = Split(Mid(shtnames, 2), "|")
any help would be appreciated
 
Last edited by a moderator:

mohadin

Active Member
Hi
Try
Code:
Dim sh As Worksheet, shtnames As String
For Each sh In ThisWorkbook.Worksheets
If Right(sh.Name, 5) = "LISTS" Then
shtnames = shtnames & "|" & sh.Name
End If
Next sh
 

KidneyStone

New Member
Hi kds, try putting:

Option Compare Text ' ignore case

at the top of the module, with this is place the code mohadin provided should work fine.
 
Top