Daily Dose of Excel

I’d like your opinion on something. I have this workbook that puts a Favorites item on the File menu (similar to the Work menu in Word). My goal was to have an easy (read: keyboard only) way to open some frequently used files without relying on the MRU. I’m trying to keep this simple by limiting it to menu commands and not having userforms or some other unnecessarily complicated user-interface.

On to my specific question. If the user (me) selects a file from the Favorites list that doesn’t exist, the app gives them options. One option is to go find the file using the GetOpenFilename method. If the user clicks Cancel on the GetOpenFilename dialog, I want to loop back around to the message box. I’ve accomplished this with a Boolean variable and a Do Loop, but it doesn’t seem very elegant.

I’d like to hear how you have or would do it, although I’m happy to hear any criticism you have about any of the code. If you want to see the code in context, you can download Favorites.zip. Be warned that this workbook is not ready for prime time. Here’s the code in question:

Sub OpenFavorite()

    Dim sCap As String
    Dim wb As Workbook
    Dim lResp As Long
    Dim sPrompt As String
    Dim sFile As String
    Dim bCancelFileOpen As Boolean
    
    sPrompt = “Workbook not found” & vbCrLf & vbCrLf & _
        ”Click Yes to find the workbook yourself, No to remove the item from the list.”
        
    With CommandBars.ActionControl
        ‘Try to open the file
        sCap = HandleAmp(Right(.Caption, Len(.Caption) - 3), False)
        On Error Resume Next
            Set wb = Workbooks.Open(sCap)
        On Error GoTo 0
        
        Do  ‘Loop until Not bCancelFileOpen
            bCancelFileOpen = False ‘Initialize
            If wb Is Nothing Then ‘if file not found
                lResp = MsgBox(sPrompt, vbYesNoCancel, “File Not Found”)
                Select Case lResp
                    Case vbYes
                        sFile = Application.GetOpenFilename(”,*.xls”)
                        ‘if user cancels, loop around and ask again
                        If sFile = “False” Then
                            bCancelFileOpen = True
                        Else
                            DeleteCurrent HandleAmp(sCap)
                            Set wb = Workbooks.Open(sFile)
                            AddCurrent
                        End If
                    Case vbNo
                        DeleteCurrent HandleAmp(sCap)
                End Select
            End If
        Loop Until Not bCancelFileOpen
    End With
    
End Sub