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]

what is a sheetname that you have that hast LISTS in the name?

here i image of what get

 

[KDS14589]

here i image of what get

Do you have the 'immediate window' displayed in the VB editor? Ctrl-G displays the 'immediate window' in the Alt-F11 VB editor

Would it be acceptable to send you the complete workbook by cloud? Or am I overreaching because this is sending me into 'fits'

 

[johnnyL]

You obviously have not been running the code that I have provided. There is no way possible that you can get your resulting names from my code.

 

[johnnyL]

Let's try one more time.

On one of your sheets, press Alt-F11.
At the top of the window that pops up, click the insert tab, then select module.
On the right side of the window that pops up, paste the following code:

Option Compare Text

Sub SheetNameTest()
    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
'
MsgBox shtnames
Debug.Print shtnames
End Sub

Then go back to one of your sheets and press Alt-F8.
In the box that pops up select 'SheetNameTest' and then select the 'Run' button.

Edit: Correction made


That code will display to a message box as well as the 'Immediate window' in the VBE.

 

[KDS14589]

what is a sheetname that you have that hast LISTS in the name?

Something is not right because you said it was displaying all sheet names, now you say it is showing none.

with the full list shown is the original code, yours show none now, i just rerun code too make sure it was your result, right now should show one sheet 'cards.lists'

 

[johnnyL]

with the full list shown is the original code, yours show none now, i just rerun code too make sure it was your result, right now should show one sheet 'cards.lists'

I adapted the code to adapt to your naming. latest code should show the one result that hast lists in the name.

 

[KDS14589]

Let's try one more time.

On one of your sheets, press Alt-F11.
At the top of the window that pops up, click the insert tab, then select module.
On the right side of the window that pops up, paste the following code:

Option Compare Text

Sub SheetNameTest()
    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
'
MsgBox shtnames
Debug.Print shtnames
End Sub

Then go back to one of your sheets and press Alt-F8.
In the box that pops up select 'SheetNameTest' and then select the 'Run' button.

Edit: Correction made


That code will display to a message box as well as the 'Immediate window' in the VBE.

 

[KDS14589]

i got a msgbox with 'ICards.lists' same in window

 

[johnnyL]

Perfect! The code works.

 

[KDS14589]

Perfect! The code works.

i reran coode and put that cde in listbox but i still get blank