Include selected sheets in a ListBox

[olem]

I try again!!! Well I use a form to list all sheets in a wokbook and it is like this:

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

However this list all sheets, but I just want to list those sheets wich have the name "plan" + "number" (e.g. plan 1, plan 2, plan 3 etc...). How can I do that????

Regards
olem

 

[Norie]

Try this.

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

With Me.ListBox1
     For Each objSheet In Sheets
          If UCase(Left(objSheet.Name, 4))="PLAN" Then
              .AddItem objSheet.Name
          End If
     Next   
     .Selected(0) = True
     .SetFocus
End With

End Sub

 

[DominicB]

Good evening

How about this :

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

With frmOle.ListBox1
For Each Item In Sheets
If Left(Item.Name, 4) = "Plan" Then
.AddItem Item.Name
End If
Next
.Selected(0) = True
.SetFocus
End With

End Sub

HTH

DominicB

 

[erik.van.geit]

!!! http://www.mrexcel.com/board2/viewtopic.php?t=235724 !!!