Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Hershey, Pennsylvania, USA
    Thanked 0 Times in 0 Posts

    A command button to pass a value between sheets? (Excel 97')

    Edited by HansV to remove superfluous line ends - the browser will wrap text automatically.

    If you could answer this (what seems a simple question, I would be eternally greatful.) I am a HS teacher creating a pretty neat spreadsheet, with student data organized into sheets by period. I then have a sort of "front end" sheet that presents a dynamic seating chart that changes throughout the day based on what time it is. It also displays whatever student information I desire, right on the student "desk" (group of cells on the "front end" sheet that display desired info, Birthday, number of days missed, latenesses, discipline summary etc.) Eventually I'll be able to randomize seating arrangements, lab groups and do some other cool stuff.

    Success so far:
    This spreadsheet hinges, on my being able to display data from the "period" sheets on the "front end" sheet. I have been successful at this using a creative mix of the Offset, Indirect, Address, Index, and Match functions and symmetrical period sheets to display desired information on the "front end sheet." Though I have no prior experience, I have also been able to put in a command button on the "front end" sheet next to each "desk". Clicking on one of these buttons Fills a box on my front end sheet that gives me the number I need for that particular student to reference on the "Period" sheet.

    My Major problem:
    How can I use a command button to Pass information from a cell on the "Front End" sheet to a desired cell on one of the "Period" sheets? I have been successful using a command button to pass
    information within the same sheet as the command button:

    Private Sub CommandButton1_Click()
    Dim TextToPass As String
    TextToPass = Range("A1")
    Range("Offset(A1,B2,C2)") = TextToPass
    End Sub

    A1 - Value I want to put in a cell
    B2 - Row of the Cell I want the value put in.
    C2 - Column of the Cell I want the value put in.
    D2 - Sheet that I want the value put in cell (B2,C2)

    This button works like a charm, but I have been unable to generalize it to allow me to pass a value between sheets. Do you have any ideas? I would be eternally greatful.

    ~ A dumfounded HS teacher in PA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: A command button to pass a value between sheets? (Excel 97')

    Welcome to Woody's Lounge!

    You can refer to cells in other worksheets in code like this:
    The name of the worksheet can be retrieved from a cell on the active worksheet:
    Post back if you need more assistance.

Posting Permissions

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