Excel: Can I Reference Sheet Codenames in VBA instead of the Index?


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.


For more resources for Microsoft Excel