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.
===========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SelRos As Integer
Dim SelCol As Integer
Dim SumCol As Integer
Dim i As Integer
Dim minCol As Integer
Dim maxCol As Integer
Dim Grade As Integer
'Get the range of columns for the grading space
minCol = Worksheets("Values").Range("C2")
maxCol = Worksheets("Values").Range("C3")
'Check the location of the selected cell to be sure that it is within the grading space
SelCol = ActiveCell.Column
SelRow = ActiveCell.Row
If SelCol < minCol Then Exit Sub
If SelCol > maxCol Then Exit Sub
SumCol = Worksheets("Values").Range("C4")
Range(Cells(SelRow, minCol), Cells(SelRow, maxCol)).ClearContents
'Get the grade value associated with the selected cell and put it in the grade cell
Grade = Sheets("Values").Cells(SelRow, SelCol).Value
Cells(SelRow, SumCol) = Grade
'Put an X in the cell selected
ActiveCell.Value = "X"
End Sub
======================================
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:
======================================
Sub CreateStuSheets()
'
' CreateStuSheets Macro
' Creates the student grade sheets based on the list of students tin the "ClassInfo" sheet. A gradesheet will be created for each record with "student" in column 2
'
Dim wks As Worksheet, wksData As Worksheet
Dim intRow As Integer, intRowL As Integer
Dim strSheet As String
Application.ScreenUpdating = False
ActiveSheet = Worksheets("ClassInfo").Activate
Set wksData = ActiveSheet
intRow = 10
On Error Resume Next
'Scan through all items in the first column until the first empty cell in column 2 is found
' looking for row with the string "student" in column 6; when found, make a new worksheet
' by copying Sheet 2 (named "Template" into a new sheet with the name from column 2
Do Until IsEmpty(wksData.Cells(intRow, 2))
If Left(wksData.Cells(intRow, 2), 7) = "student" Then
Set wks = Worksheets(wksData.Cells(intRow, 4).Value)
If Err > 0 Or wks Is Nothing Then
Err.Clear
Worksheets("StuMaster").Copy After:=Worksheets(Worksheets.Count)
'Range("A1") = "Data"
ActiveSheet.Name = wksData.Cells(intRow, 4)
End If
End If
intRow = intRow + 1
Loop
Application.ScreenUpdating = True
End Sub
==============================================
Bookmarks