Results 1 to 2 of 2
2004-07-17, 20:12 #1
- 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
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
2004-07-17, 20:18 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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.