Active sheet shifts back to original sheet when executing code

**pcdog** · 10-21-2012, 09:30 AM

I developed a spreadsheet form for grading student's work for an entire quarter on one sheet. I use the ChangeSelection function to allow a teacher to click a cell that put a particular grade value into the grade total. This works fine on the template sheet where I developed and tested it. I then replicate that sheet to create several student pages based on a roll list in Sheet1. When I click the desired cell in the copied sheet, the ActiveSheet jumps back to the template sheet and posts the grade there. I want the active sheet to remain the sheet for the selected student and execute the the correct grade storage operation on that sheet.

How can I keep the student sheet as the Active Sheet or force the Active Sheet back to the selected student sheet before executing the grade placement function?

I've tried creating a global variable (Dim CurrentSheet As Sheet) and setting it when a SheetActivate event occurs, this, however, causes CurrentSheet to get set to the template when a new cell is selected in the student sheet. Is there a way to move the code from the template sheet to the workbook so that it applies to each sheet as it is selected?

**Norie** · 10-21-2012, 09:52 AM

Can you post the code?

**Jakobshavn** · 10-21-2012, 09:53 AM

Post your code or share your workbook.

**pcdog** · 10-21-2012, 11:28 AM

Originally Posted by Jakobshavn

Post your code or share your workbook.

Sorry, but I don't know how to share a workbook on this forum or how to post the entire image of code window (other than including a screen
capture image)

Here is the code that processes the SelectionChange event (the top of the VME window shows "Sheet2(Code)" (Sheet2 is the template sheet)
if I set a breakpoint in this code, when I click on a cell in the copied sheet, the selected sheet changes to Sheet2 but this code does not
execute until I select a cell in Sheet2.

===========================================

Please Login or Register  to view this content.

======================================

Evidently, this code is only associated with the template sheet and does not get associated with the copy of the template sheet that I create
for each student. Somehow selecting a cell in the copied sheet causes the template sheet to be activated. Here is the code that I use to create
the new student work sheets: (sheet "StuMaster" contains the student name list; each student record is tagged with the label "student" in column 2:

======================================

Please Login or Register  to view this content.

==============================================

**Norie** · 10-21-2012, 11:36 AM

What's the name of the Template sheet?

**Jakobshavn** · 10-21-2012, 11:38 AM

If the event macro is in each of the copied sheets and you want it to operate only on that active, copied sheet, then remove all references to:

Worksheets("Values")

**pcdog** · 10-21-2012, 11:56 AM

Originally Posted by Jakobshavn

If the event macro is in each of the copied sheets and you want it to operate only on that active, copied sheet, then remove all references to:

Worksheets("Values")

I'm not sure if the event macro is in each of the copied sheets - it doesn't appear to be so because when I select a new cell in a copied sheet, the template sheet ("StuMaster")
is immediately selected but the macro for SelectionChange is not executed until I click on a cell in the StuMaster sheet.

How would I make sure that the macro is copied into each new sheet?

If I don't reference WorkSheet(Values) how would I get the cell range numbers? Put them in global variables? that seems a bit clumsy in that I would have to anticipate every
control value that I would normally communicate through the "Values" worksheet.

**pcdog** · 10-21-2012, 11:56 AM

Template sheet name : "StuMaster"

**Norie** · 10-21-2012, 12:05 PM

Can you attach a sample workbook?

**pcdog** · 10-21-2012, 12:08 PM

I tried removing all references to Worksheets("Values") by hardcoding the minCol, maxCol and sumCol values - this did not change the behavior of the WorkBook - still jumps to the
sheet "stuMaster" (the original template sheet) when I click a cell in one of the copied work sheets.

**pcdog** · 10-21-2012, 12:09 PM

Originally Posted by Norie

Can you attach a sample workbook?

Would love to - how do I do attach a sample workbook?

**Norie** · 10-21-2012, 12:11 PM

Click Go Advanced and then goto Manage Attachments.

**pcdog** · 10-21-2012, 01:02 PM

Originally Posted by Norie

Click Go Advanced and then goto Manage Attachments.

Okay - here goes:

The first three sheets are reserved for control parameters (none used yet), student data, and values to be used to define the rubric
forms and values for grading. The remaining sheets are student data sheets where the grades will be posted and calculated.

If the workbook works correctly, you should be able to select a student sheet, then click on a cell across from a graded characteristic and
under the desired grade. The cell should be marked with an "X" and the grade value entered in the Point Total column. The grade is calculated
as a percentage of the maximum possible (all "A" column checks).

**Cutter** · 10-21-2012, 01:05 PM

@ pcdog

Welcome to the forum.

Please notice that [CODE] tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.

**Norie** · 10-21-2012, 03:30 PM

pcdog

In workbook code you have this in each sub, which is activating the template.

Please Login or Register  to view this content.

This could be the problem as any code following that line that doesn't have a worksheet reference will reference the template.

For example, a few lines down you have this.

Please Login or Register  to view this content.

That code is going to refer to the template sheet.

Is it supposed to?

**pcdog** · 10-21-2012, 04:08 PM

Duh! - Light slowly dawning - Thanks for the VBA lesson

This looks like it is probably the problem. I did not realize that VBA had done what I wanted in that it copied the code for each sheet so I did not open each sheet to see what might be happening. I was modifying code that only applied to the template sheet ("StuMaster") thinking that it was the one copy of the code that was controlling everything. This means that I will need to regenerate the entire gradebook every time I fix a bug or make a mod to the way that the grading modules work. Is there a way to have a master code that handles the control of every sheet? Could I place that code in the WorkBook code area and adjust a pointer to the active worksheet for each function? That was sort of the way that I thought things were working?

I'll experiment and research some more.

Thanks again.

**pcdog** · 10-21-2012, 08:53 PM

Duh! - Light slowly dawning - Thanks for the VBA lesson

This looks like it is probably the problem. I did not realize that VBA had done what I wanted in that it copied the code for each sheet so I did not open each sheet to see what might be happening. I was modifying code that only applied to the template sheet ("StuMaster") thinking that it was the one copy of the code that was controlling everything. This means that I will need to regenerate the entire gradebook every time I fix a bug or make a mod to the way that the grading modules work. Is there a way to have a master code that handles the control of every sheet? Could I place that code in the WorkBook code area and adjust a pointer to the active worksheet for each function? That was sort of the way that I thought things were working?

I'll experiment and research some more.

Thanks again.

**Jakobshavn** · 10-21-2012, 10:21 PM

If you have programming that is common to all sheets, rather than put a copy in each sheet, put the common code in a module and have the event code call the module-based sub.

This way you will only have to maintain a single copy.

**pcdog** · 10-21-2012, 10:42 PM

That is what I have done - looks like it is working a charm!

Thanks for the help.

**Cutter** · 10-22-2012, 09:15 AM

@ pcdog

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Norie** · 10-22-2012, 11:07 AM

You can also use the workbook level event SheetSelectionChange.

If you use that you don't need code in the worksheets.

Active sheet shifts back to original sheet when executing code

LinkBack

Thread Tools

Rate This Thread

Display

Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Re: Active sheet shifts back to original sheet when executing code

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions