Sheet Lookup

[Greg B]

I was wondering what code would I use on a userform to lookup up all sheets
with the name "round" in it. i.e. "round 1". I have about 60 worksheets in
this program and I want the ability just to look back over previous results.
Also what would I use on the user a list box or combo box to do this?


Thanks in advance

Greg

[William Benson]

Private Sub UserForm_Initialize()
GetSheetsWithRound
End Sub
Sub GetSheetsWithRound()
Dim Sht As Worksheet
Dim ShtArr() As String
Dim i As Long
ReDim ShtArr(0)
For Each Sht In ThisWorkbook.Worksheets
If InStr(UCase(Sht.Name), "ROUND") <> 0 Then
ReDim Preserve ShtArr(UBound(ShtArr) + 1)
ShtArr(UBound(ShtArr)) = Sht.Name
End If
Next Sht

'Not you have a nice Array of sheet names which you can loop through to
populate the combo box using the
'.AddItem method
For i = 1 To UBound(ShtArr)
ComboBox1.AddItem ShtArr(i)
Next i
End Sub


"Greg B" <[email protected]> wrote in message
news:[email protected]...
>I was wondering what code would I use on a userform to lookup up all sheets
> with the name "round" in it. i.e. "round 1". I have about 60 worksheets
> in
> this program and I want the ability just to look back over previous
> results.
> Also what would I use on the user a list box or combo box to do this?
>
>
> Thanks in advance
>
> Greg
>
>

[Greg B]

Brilliant Thanks William.

Greg
"William Benson" <[email protected]> wrote in message
news:[email protected]...
> Private Sub UserForm_Initialize()
> GetSheetsWithRound
> End Sub
> Sub GetSheetsWithRound()
> Dim Sht As Worksheet
> Dim ShtArr() As String
> Dim i As Long
> ReDim ShtArr(0)
> For Each Sht In ThisWorkbook.Worksheets
> If InStr(UCase(Sht.Name), "ROUND") <> 0 Then
> ReDim Preserve ShtArr(UBound(ShtArr) + 1)
> ShtArr(UBound(ShtArr)) = Sht.Name
> End If
> Next Sht
>
> 'Not you have a nice Array of sheet names which you can loop through to
> populate the combo box using the
> '.AddItem method
> For i = 1 To UBound(ShtArr)
> ComboBox1.AddItem ShtArr(i)
> Next i
> End Sub
>
>
> "Greg B" <[email protected]> wrote in message
> news:[email protected]...
> >I was wondering what code would I use on a userform to lookup up all
sheets
> > with the name "round" in it. i.e. "round 1". I have about 60 worksheets
> > in
> > this program and I want the ability just to look back over previous
> > results.
> > Also what would I use on the user a list box or combo box to do this?
> >
> >
> > Thanks in advance
> >
> > Greg
> >
> >
>
>