Select sheets with ListBox!?

[olem]

I'm trying to use a listbox to find and select certain sheets in my workbook. I've made a userform with following code:

Private Sub UserForm_Initialize()
Dim objSheet As Object
CommandButton1.Caption = "Ok"
CommandButton2.Caption = "Cancel"

With frmOle.ListBox1
For Each Item In Sheets
.AddItem Item.Name
Next
.Selected(0) = True
.SetFocus
End With

End Sub

How do I go forward to make the selected item in the listbox
activate and show the correct sheet, and further is it possible to "hide" certain sheets from the listbox (not selectable sheets). (I have multipple sheets and amount of sheets in the workbook is not static but increases).

Please help, and thx in advance
Regards OleM

 

[erik.van.geit]

Hi, olem
Welcome to the Board !!!!!

try this line in your "ok"button code

Sheets(ListBox1.Text).Select

is it possible to "hide" certain sheets from the listbox (not selectable sheets)

of course it is possible
what would be the criteria to exclude them from the list ?

kind regards,
Erik

 

[olem]

Thx!! For quick answer!!!
About criteria
Its maybe easier to think about the criterias which includes the sheets in the list. The workbook create plans, plan 1, plan 2 etc... So I want the list to include all sheets which contains the text "plan" & "number" (and exclude all other sheets).

Another thing?!:
Is it possible to make the same list when the VBOLock library is missing?
My pc at work do not handle the code "item".

Regards OleM

 

[erik.van.geit]

try this

Private Sub UserForm_Initialize()
Dim sh As Worksheet

    With Me.ListBox1
        For Each sh In Sheets
        'one of these lines
        'check specific cell
        If InStr(1, sh.Range("A1"), "plan") Then .AddItem sh.Name
        'find anywhere on sheet
        'If Not sh.UsedRange.Find("plan", lookat:=xlPart, MatchCase:=False) Is Nothing Then .AddItem sh.Name
        Next
    .Selected(0) = True
    .SetFocus
    End With

End Sub

regarding the variable "item"
avoid using terms like Object, Areas, Str, Filter, Title, Name, Item, Time, since they are used (needed) by VBA itself: that's why the first character changes in an uppercase automatically!


About "Item" (or "Name", "Time", ...)
click in that word within your code and press F1
the Help will appear on that item
sometimes this specific word is needed by VBA itself, that's why we don't use it

test this:
type: name = "excel"
pressing ENTER it will change in Name = "excel" (see the uppercase!)

you can do the same with item, ...

you see ?

From VBA Help on Visual Basic Naming Rules:

"Generally, you shouldn't use any names that are the same as the functions, statements, and methods in Visual Basic. You end up shadowing the same keywords in the language. To use an intrinsic language function, statement, or method that conflicts with an assigned name, you must explicitly identify it. Precede the intrinsic function, statement, or method name with the name of the associated type library. For example, if you have a variable called Left, you can only invoke the Left function using VBA.Left."

best regards,
Erik

 

[olem]

Thx!!!

Thanks again it was most helpful!! And it works!
Olem