Select sheets with ListBox!?

olem

New Member
Joined
Oct 1, 2006
Messages
12
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, olem
Welcome to the Board !!!!!

try this line in your "ok"button code
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

New Member
Joined
Oct 1, 2006
Messages
12
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
try this
Code:
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

New Member
Joined
Oct 1, 2006
Messages
12
Thx!!!

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

Watch MrExcel Video

Forum statistics

Threads
1,113,791
Messages
5,544,302
Members
410,601
Latest member
Silver2
Top