Thread: Excel 2007 VBA Headache - Things not doing what they should...

I took c++ about 10 years ago and just started teaching myself VBA in Excel 2007 a couple of days ago in order to make a semi-automated grade sheet (max 94 students, 6 lab sections, 4 exams) to streamline the grade compiling process, reduce error, and refresh my programing logic skills. I have several buttons connected to macros that perform various tasks such as create separate lab sheets, print off various data sets, and import exam and lab grades from various workbooks. The particular button I am having problems with will allow for the current workbook to be saved in a new directory.

The sub is triggered by the button and it creates the directory if it doesn't exist and saves a copy of the workbook in the new directory. It also calls a function to evaluate if the directory exists or not and giving messages when needed and returning either true or false, every button references this function as I only want some to work in the new copy, and some to only work in the "template." The fourth value (Boolean) is what determines whether its for the new copy or for the template. The basic issue is that when the fourth value is false, it works as it should in both instances, but when the value is set to true it has issues.

It goes through the code and you can see all my test message boxes that I use to verify that it goes to the right value. And even when it has the proper value at the end of the function (CheckSheetExist = true) but the function returns false. Any insight or coding tips are welcome. Thank you very much for your time and have a good day.

Sub CreateMasterGradeSheet()
Dim classyear As String
Dim mastername As String
Dim newdir As String
Dim masterbook As String
Dim test As Boolean

classyear = Worksheets("Name and Code Master").Range("G9")
test = CheckSheetExist(ThisWorkbook.name, ThisWorkbook.path, classyear, True)
MsgBox ("test = " & test)
If CheckSheetExist(ThisWorkbook.name, ThisWorkbook.path, classyear, True) Then
    newdir = ThisWorkbook.path + "\" + classyear
    If (Dir(newdir, vbDirectory) = "") Then
        MkDir newdir
    End If
    mastername = ThisWorkbook.name
    masterbook = newdir + "\" + classyear + " " + mastername
    If (Dir(masterbook) = "") Then
        Workbooks(mastername).SaveAs Filename:=masterbook
    Else
        MsgBox ("A Master Grade Sheet for the " & classyear & " semester already exists. " & vbCrLf & "Please open it if you wish to process any data or delete it." & vbCrLf & "Location: " & masterbook)
    End If
Else
    MsgBox ("****")
End If

End Sub

Function CheckSheetExist(ByVal name As String, ByVal path As String, ByVal classyear As String, ByVal bool As Boolean) As Boolean
Dim file As String
Dim classname As String
classname = classyear + " " + name
file = path + classname

If name = "Master Grade Sheet.xlsm" Then
    MsgBox ("Test 1")
    If bool = False Then
        If (Dir(file) = "") Then
            file = path + "\" + classyear + "\" + classname
            If (Dir(file) = "") Then
                MsgBox ("A Master Grade Sheet for the " & classyear & " semester has not been created yet. " & vbCrLf & "Please first create a Master Grade Sheet for that semester.")
            Else
                MsgBox ("A Master Grade Sheet for the " & classyear & " semester already exists. " & vbCrLf & "Please open it if you wish to process any data." & vbCrLf & "Location: " & file)
            End If
        Else
            MsgBox ("A Master Grade Sheet for the " & classyear & " semester already exists and is in the same folder as the template (Master Grade Sheet.xlsm). " & vbCrLf & "Please make sure that the template is in the parent folder of all of the semester Master Grade Sheets." & vbCrLf & "Location: " & file)
        End If
        MsgBox ("Test 3")
        CheckSheetMaster = False
    Else
        MsgBox ("Test 4")
        CheckSheetMaster = True
    End If
Else
    MsgBox ("Test 5")
    If bool = True Then
        If name = classyear + " Master Grade Sheet.xlsm" Then
            MsgBox ("This Master Grade Sheet for the" & classyear & " semester has already been created. " & vbCrLf & "Please use any of the other buttons to process data or return to the template (Master Grade Sheet.xlsm) to create a Master Grade Sheet for a different semester.")
        Else
            MsgBox ("The semester value ('Name and Code Master'G9) does not match the file name: " & name & ". " & vbCrLf & "Please change the semester value to match the file name and use any of the other buttons to process data, or return to the template (Master Grade Sheet.xlsm) to create a Master Grade Sheet for the " & classyear & " semester.")
        End If
        MsgBox ("Test 6")
        CheckSheetMaster = False
    Else
        MsgBox ("Test 7")
        CheckSheetMaster = True
    End If
End If
    MsgBox (CheckSheetMaster)
End Function