Restrict search for VBA listbox

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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



VBA Code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Based off the first code example:

VBA Code:
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
        End If
    Next sh
'
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub
 

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows
Based off the first code example:

VBA Code:
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
        End If
    Next sh
'
    ListBox1.List = Split(Mid(shtnames, 2), "|")
End Sub
i still get a full list of ALL worksheets
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You will get a list of all worksheet names that end in 'LISTS'. Is that not what you wanted?
 

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You will get a list of all worksheet names that end in 'LISTS'. Is that not what you wanted?
yes that's what i want but i'm getting a FULL list of ALL WORKSHEETS even ones without LISTS in their name
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Put the following code into a separate module and then run it.

VBA Code:
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
'
Debug.Print shtnames
End Sub

What does the debug 'Immediate' window show?
 

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
138
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You will get a list of all worksheet names that end in 'LISTS'. Is that not what you wanted?
attached is image ofwhat I'm getti
Put the following code into a separate module and then run it.

VBA Code:
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
'
Debug.Print shtnames
End Sub

What does the debug 'Immediate' window show?
i show nothng
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Do you have the 'immediate window' displayed in the VB editor? Ctrl-G displays the 'immediate window' in the Alt-F11 VB editor
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,583
Messages
5,707,207
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top