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

 

[johnnyL]

You probably didn't put the top line of code I provided as the top line of all of your code.

Your code based on the original code should look like the following:

Option Compare Text


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)
    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()
'
    Dim sh As Worksheet, shtnames As String
'
    For Each sh In ThisWorkbook.Worksheets
        If Right(sh.Name, 5) = "LISTS" Then shtnames = shtnames & "|" & sh.Name
    Next sh
''
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub

 

[KDS14589]

You probably didn't put the top line of code I provided as the top line of all of your code.

Your code based on the original code should look like the following:

Option Compare Text


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)
    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()
'
    Dim sh As Worksheet, shtnames As String
'
    For Each sh In ThisWorkbook.Worksheets
        If Right(sh.Name, 5) = "LISTS" Then shtnames = shtnames & "|" & sh.Name
    Next sh
''
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub

with your code inn thatt listbox code wndow i get 'debug' error 424 object required and code highlited

Sub Edit_Lists_Click()
Edit_Lists_UF.Show
End Sub

 

[johnnyL]

Ok, provide a link to the file that showed all worksheets so we can take a look at it.

 

[KDS14589]

Ok, provide a link to the file that showed all worksheets so we can take a look at it.

ok give mme a few

 

[KDS14589]

ok give mme a few

i hope this works never tried this

9.10.21 Cards v4 .xlsm

Shared with Dropbox

www.dropbox.com

 

[johnnyL]

I don't have dropbox.

Try uploading it here <---

Then provide the link it gives you.

 

[KDS14589]

I don't have dropbox.

Try uploading it here <---

Then provide the link it gives you.

its there I hope

 

[johnnyL]

You have to provide the link to the file after you have uploaded it. The link to the file will have like 6 letters on the end of it.

 

[KDS14589]

You have to provide the link to the file after you have uploaded it. the link to the file will have like 6 letters on the end of it.

Gofile - Free file sharing and storage platform sorry

 

[johnnyL]

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.