+ Reply to Thread
Results 1 to 2 of 2

Showing Spreadsheet values on a userform

  1. #1
    jamie.rowland@shell.com
    Guest

    Showing Spreadsheet values on a userform

    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


  2. #2
    moon
    Guest

    Re: Showing Spreadsheet values on a userform


    '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




    <jamie.rowland@shell.com> schreef in bericht
    news:1154698750.066814.91990@h48g2000cwc.googlegroups.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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1