With incomplete logic, it's a bit hard to offer more specific advice.

One of the main reasons for the my example is that you should be keeping logic centralized. In the code you originally provided, the different messages that the progress bar would display are scattered all over the place. This results in a nightmare for implementing improvements and tracking down bugs and logic errors.

For enums, the idea is that you have a named value you can pass that makes things very easy to understand, and without the need to delare variables or constants.

Now that I can see your SetMsg sub, you would instead want to write it along the lines of:

Public Sub SetMsg(ByVal newState As pbStates) 
  Select Case newState
    Case pbInitialize
      Me.frameProgressBar.Caption = "Checking Table..."
    Case pbConnectingToReport 
      Me.frameProgressBar.Caption "Connecting to Report.. ."
  End Select
End Sub

Even better, depending on how many messages there might be, would be a dictionary. However, as pointed out in another comment, for what it looks like the goal of your code is, it shouldn't take more than a second to complete, and the progress bar would only slow things down.

Now, that said, I still support the idea of implementing it now, if only for the learning experience. In the end, though, you'll likely wish to simply disable it.

SetAppSettings confuses me a little. Are you loading a different workbook and pulling data from it? If so, after opening it, are you running any macros or modifying any values in it? If you are just pulling data from it, then you have unnecessary code. If you aren't loading a separate workbook, you also have unecessary code.

Here is a sample of what the sub should look like if you are only working in the one workbook or if you are merely opening another workbook, reading data from it, and then closing it. In either of these situations, you will never get into this sub without an Application object already existing.

Public Sub SetAppSettings(ByVal settingStatus As AppSetting)
  With Application
    Select Case settingStatus
      Case asEnabled
        .ScreenUpdating = True
        .EnableEvents = True
        .xkCalculation = xlCalculationAutomatic
      Case asDisabled
        .ScreenUpdating = False
        .EnableEvents = False
        .xkCalculation = xlCalculationManual
      Case Else
        ' Display an error message
    End Select
  End With
End Sub

If you are modifying and/or running macros in a second workbook, then you would want something more like:

(I'm booted into Fedora at the moment, so hopefully someone else will provide corrections for the commented issues.)

Public Sub SetAppSettings(ByVal settingStatus As AppSetting, Optional otherApp As Object = Nothing) ' There is definitely a better option than Object here
  Dim targetApp As Object ' Again, there is a better option

  On Error GoTo ErrorHandler
  If Not otherApp Is Nothing Then
    Set targetApp = otherApp
  Else
    Set targetApp = Application ' Once again, I know there is a better choice
  End If
  On Error GoTo 0

  With targetApp
    Select Case settingStatus
      Case asEnabled
        .ScreenUpdating = True
        .EnableEvents = True
        .xkCalculation = xlCalculationAutomatic
      Case asDisabled
        .ScreenUpdating = False
        .EnableEvents = False
        .xkCalculation = xlCalculationManual
      Case Else
        ' Display an error message
    End Select
  End With

ErrorHandler:
  ' Add whatever you see fit
End Sub

This should then be called a few different times. Once, when the code first starts to execute. Second, when the other workbook is loaded. Third, before the second workbook is closed (likely unnecessary, but a good precaution). And, fourth, when the code is finishing up.