Restrict search for VBA listbox

[KDS14589]

I'm using code I received lately and now trying to restrict it to only worksheets ending in LISTS for a new listbox on a new userform.

The original code is

Private Sub CommandButton1_Click()



Dim i As Integer, sht As String

For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) = True Then

sht = ListBox1.List(i)



End If

Next i

Sheets(sht).Visible = True

ShGE04.Visible = False

Sheets(sht).Activate

End

End Sub



Private Sub CommandButton2_Click()

Unload Go_To_WS

End Sub





Private Sub UserForm_Initialize()

''''NoSparks @ Mr Excel 9.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

My new NONWORKING code is

Private Sub CommandButton1_Click()



Dim i As Integer, sht As String

For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) = True Then

sht = ListBox1.List(i)



End If

Next i

Sheets(sht).Visible = True

ShGE04.Visible = False

Sheets(sht).Activate

End

End Sub



Private Sub CommandButton2_Click()

Unload Edit_Lists_UF

End Sub



Private Sub UserForm_Initialize()



Dim sh As Worksheet, shtnames As String

Dim ws As Worksheet

For Each sh In ThisWorkbook.Worksheets

If ws.Name Like "*.lists" Then

shtnames = ws.Name

End If

Next sh



End Sub

I'm attempting to have a VBA listbox that shows only worksheets that end in .LISTS

I've checked other forums searches (along with yours) and the web about listboxs but they seem to involve results from other sources. So simple a task and I'm lost.

After many fail attempts, I'm asking for help again. THANKS

 

[KDS14589]

The file you uploaded errors on the 'Edit Lists' button. I need a version that doesn't error on that, and shows all sheet names like you said it did at one time.

ok

 

[KDS14589]

ok

try this no error and full list Gofile - Free file sharing and storage platform

 

[KDS14589]

try this no error and full list Gofile - Free file sharing and storage platform

his oone with error Gofile - Free file sharing and storage platform

 

[johnnyL]

Ok, this is the updated file to the link in post # 32 that only shows sheets that end in '.lists':

\/ \/ \/
Updated File

 

[KDS14589]

Ok, this is the updated file to the link in post # 32 that only shows sheets that end in '.lists':

\/ \/ \/
Updated File

i checked it out and all good now
MILLION THANKS
PS if you have time, what did i do wrong?

 

[johnnyL]

This is the only code change I made to the file that you uploaded:

Private Sub UserForm_Initialize()
'
    Dim sh As Worksheet, shtnames As String
'
    For Each sh In ThisWorkbook.Worksheets
        If StrComp((Right(sh.Name, 6)), ".LISTS", vbTextCompare) = 0 Then shtnames = shtnames & "|" & sh.Name
    Next sh
'
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub

You could compare that code to post #2 in this thread, I guess.


It basically does the same things that what I had been posting, but it incorporates the 'Option Compare Text' directly into the code, negating the need for the separate line at the top of all code.

The 'vbTextCompare' portion replaces the 'Option Compare Text' & makes the comparison case insensitive. IE. Fr would = fR, etc

I also incorporated the 'StrComp' to do the actual comparison.

 

[KDS14589]

This is the only code change I made to the file that you uploaded:

Private Sub UserForm_Initialize()
'
    Dim sh As Worksheet, shtnames As String
'
    For Each sh In ThisWorkbook.Worksheets
        If StrComp((Right(sh.Name, 6)), ".LISTS", vbTextCompare) = 0 Then shtnames = shtnames & "|" & sh.Name
    Next sh
'
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub

You could compare that code to post #2 in this thread, I guess.


It basically does the same things that what I had been posting, but it incorporates the 'Option Compare Text' directly into the code, negating the need for the separate line at the top of all code.

The 'vbTextCompare' portion replaces the 'Option Compare Text' & makes the comparison case insensitive. IE. Fr would = fR, etc

I also incorporated the 'StrComp' to do the actual comparison.

THANKS again