'this one fills the listbox (named cbo) with all
'available sheet names
Private Sub UserForm_Initialize()

'object variables
Dim wb As Workbook 'workbook
Dim sc As Sheets 'sheets collection
Dim ws As Worksheet 'single worksheet

Set wb = ThisWorkbook
Set sc = wb.Sheets

'walk through sheets collection
For Each ws In sc

'add each name to the listbox
cbo.AddItem ws.Name

Next ws

Set ws = Nothing
Set sc = Nothing
Set wb = Nothing

End Sub


'what if we click the listbox?
Private Sub cbo_Click()

'your objects again
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets(cbo.Text)

ws.Activate

'and update the form values
txtH6.Value = ws.Cells(6, 8).Value
txtH7.Value = ws.Cells(7, 8).Value
txtH8.Value = ws.Cells(8, 8).Value
'...

Set ws = Nothing
Set wb = Nothing

End Sub




schreef in bericht
ups.com...
Hi,

I am trying to create a user form that shows certain values from
specific cells when a specific worksheet is selected.

i.e. the information is summary information in a worksheet relating to
a team of people and their working return (sales).

I currently use a form to input most recent data on each team. Each
worksheet then has a summary section which totals and averages some of
the data.

IS there any way that I can create a form that allows me to use a cbo
to select a team (or worksheet) and as a result shows the summary data
on the form (i.e. saves me from sifting through the workbook and
worksheets!?)

cbo - cboTeam (list that allows me to pick relevant worksheet)

summary data is in Cell H5, H6, H7, I5, I6, I7 and J8

Presumably I can set up txtboxes as spaces to show the values - I have
done this and used the following nomenclature...

TxtH6, TxtH7 etc.

Can anyone help