I have this spreadsheet I pieced together, and I've got it working pretty well. The one thing I can't seem to figure out through all the reading I've done is how to use the Sheet Codenames in VBA to do what this currently does IF a teacher accidentally moves sheets out of order. It's happened only twice, but I'd like to try and prevent it IF I am able to understand anyone's suggestions :) Here is all the code for this button. I am wondering if there is a way to replace the worksheet references that I highlighted in BOLD and UNDERLINED text to use the codenames for the sheets. Each student has two associated sheets, which I used the VBE to rename the codenames to S1ReportCard, S1CheckList for student 1, etc.
Private Sub CommandButton1_Click() 'Code for Blue Column Individual Creation Dim txtSheet As String Dim txtSheet2 As String Dim txtSheet3 As String Dim txtSheet4 As String Dim txtSheet5 As String Dim txtSheet6 As String Dim mainworkbook As Workbook Dim cntsheets As Integer Dim cntPrint As Integer Dim ws As Integer Dim intMsgBox As Integer Dim strSaveName As String Dim OutputFolderName As String Dim SID As String Dim SIDB As String Dim SIDC As String Dim SIDN As Integer Dim cntStudent As Integer Dim SNC As Integer Dim SNCN As String Set mainworkbook = ActiveWorkbook cntsheets = mainworkbook.Sheets.Count 'Count number of sheets in workbook cntPrint = cntsheets - 5 'Subtract counter by number of grade sheets (currently 5) cntPrint = cntPrint / 2 'Divide by two since two sheets per student ws = 6 'Start First Student at worksheet 6 cntStudent = 0 'Set Student Count to Zero SID = "E9" 'Set First Student Create Report Card Cell SIDN = 9 'Set First Student Create Report Card Counter SIDB = "B9" 'Set First Student Last Name Cell SIDC = "C9" 'Set First Student First Name Cell SNC = 70 'Set First Cell for Valid Student Name SNCN = "A70" 'set grade sheets to default files names to be copied and hidden txtSheet3 = VocabSpell.Name 'Vocab-Spelling txtSheet4 = Math.Name 'Math txtSheet5 = Reading.Name 'Reading txtSheet6 = ELA.Name 'ELA Line2: 'Assign Folder or Drive to save files in OutputFolderName = "" Set myDlg = Application.FileDialog(msoFileDialogFolderPicker) myDlg.AllowMultiSelect = False myDlg.Title = "Please Select Default Folder to Save Report Cards to:" If myDlg.Show = -1 Then OutputFolderName = myDlg.SelectedItems(1) 'Check to see if \ exists, and if not, add one If Right(OutputFolderName, 1) = "\" Then Set myDlg = Nothing GoTo Line3: Else OutputFolderName = OutputFolderName & "\" Set myDlg = Nothing End If Else 'If User does not select folder or drive, ask to be sure intMsgBox = MsgBox("You MUST select a default Folder to Save to continue. Select YES to reselect folder or NO to quit", vbYesNo, "Default Folder Needed") Select Case intMsgBox Case vbYes GoTo Line2: Case vbNo Exit Sub End Select End If Line3: intMsgBox = MsgBox("To Create Report Cards for INDIVIDUAL Students marked YES(Y)in the BLUE COLUMN, Click YES, Otherwise Click NO or CANCEL to abort", vbYesNoCancel, "Create ALL Report Cards at Once?") Select Case intMsgBox Case vbNo MsgBox "Process Aborted", vbOKOnly, "No Reports Cards Created" Exit Sub Case vbCancel MsgBox "Process Aborted", vbOKOnly, "No Reports Cards Created" Exit Sub Case vbYes For i = 1 To cntPrint 'Set Counter to cycle through all students If Range(SID).Value = "Y" Then If Range(SNCN) = "" Then MsgBox "Student Number " & i & " Has an Invalid Last and First Name, Report Card Not Created", vbOKOnly, "Invalid Name" GoTo Line1: End If Worksheets(ws).Select txtSheet = Worksheets(ws).Name 'Assign sheet1 selected name to use for file name on next line strSaveName = OutputFolderName & Worksheets(ws).Range("C3").Value & ".xlsx" ws = ws + 1 'advance counter by one to select mating checklist Worksheets(ws).Select (False) txtSheet2 = Worksheets(ws).Name 'Assign sheet2 with selected checklist sheet name cntStudent = cntStudent + 1 'Increase Student Counter as cards are made Sheets(Array(txtSheet, txtSheet2, txtSheet3, txtSheet4, txtSheet5, txtSheet6)).Copy Worksheets(1).Visible = xlSheetHidden Worksheets(2).Visible = xlSheetHidden Worksheets(3).Visible = xlSheetHidden Worksheets(4).Visible = xlSheetHidden ActiveWorkbook.SaveAs strSaveName 'Save new file ActiveWorkbook.Close 'close new file Else ws = ws + 2 'Advance two sheets when Student is not selected SIDN = SIDN + 1 SID = "E" & SIDN SNC = SNC + 1 'Advance to next student name SNCN = "A" & SNC GoTo Line4: End If Line1: 'Advance to Next Student Range(SID) = "N" SIDN = SIDN + 1 SID = "E" & SIDN ws = ws + 2 'Advance to next student SNC = SNC + 1 'Advance to next student name SNCN = "A" & SNC Line4: Next i If cntStudent = 0 Then MsgBox "No Report Card Files Have Been Created", vbOKOnly, "Files Not Created" Else MsgBox "Excel Files For The/(All) Selected " & cntStudent & " Student(s) Have Been Created", vbOKOnly, "Files Successfully Created" End If 'Reselect the control Sheet after report cards created ws = 1 Worksheets(ws).Select Exit Sub End Select End Sub
This question generated 15 answers. To proceed to the answers, click here.
This thread is current as of March 22, 2018.