Excel 2010 VBA MsgBox looping

[THNDRacket]

I have a mysterious problem in which a MsgBox infinitely displays after clicking its buttons. What is really strange is that the next portion of code executes, but doesn't exit the If statement.

Sub XcaliburCSV()
    
    Dim XQNFile, ProcMet, InstMet As String

    ThisWorkbook.Sheets("Sheet1").Activate

    NewCalib = MsgBox("Is this a new calibration?", vbYesNo, "XCalibur Sequence Creation")

    If NewCalib = vbYes Then   'NewCalib = 6 for vbYes, 7 for vbNo
        Cells(1, 5) = "Yes"
        Bracket = "Bracket Type=4"
        Sheets("Sheet1").Rows("4:7").Hidden = False
        MsgBox ("NewCalib = " & NewCalib & vbCr & "Bracket = " & Bracket)
    Else
        Cells(1, 5) = "No"
        Bracket = "Bracket Type=2"
        Sheets("Sheet1").Rows("4:7").Hidden = True
    End If
    
    XQNFile = SelectFile("Select Calibration File...", "*.XQN")
    InstrMet = SelectFile("Select Instrument Method...", "*.meth")
    ProcMet = SelectFile("Select Processing Method...", "*.pmd")
        
    With ActiveWorkbook.Sheets("Sheet1")
        .Range("B20") = XQNFile
        .Range("B21") = InstrMet
        .Range("B22") = ProcMet
    End With
    
    ThisWorkbook.Sheets("Xcalibur Sequence").Cells(1, 1).Value = Bracket    
    
End Sub

Cell E5 changes accordingly, but the initially hidden rows stay hidden regardless, and the YesNo MsgBox reappears in an infinite loop. What am I missing?

 

[Norie]

What happens in SelectFile?

 

[AlphaFrog]

Do you have any event macros like Worksheet_Change, or Workbook_Activate, etc. There seems to be some other code you have not displayed that is triggered.

If yes, perhaps something like this (guessing).

    [B]Application.EnableEvents = [color=darkblue]False[/color][/B]
    [color=darkblue]If[/color] NewCalib = vbYes [color=darkblue]Then[/color]   [color=green]'NewCalib = 6 for vbYes, 7 for vbNo[/color]
        Cells(1, 5) = "Yes"
        Bracket = "Bracket Type=4"
        Sheets("Sheet1").Rows("4:7").Hidden = [color=darkblue]False[/color]
        MsgBox ("NewCalib = " & NewCalib & vbCr & "Bracket = " & Bracket)
    [color=darkblue]Else[/color]
        Cells(1, 5) = "No"
        Bracket = "Bracket Type=2"
        Sheets("Sheet1").Rows("4:7").Hidden = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [B]Application.EnableEvents = [color=darkblue]True[/color][/B]

 

[THNDRacket]

SelectFile simply uses msoFileDialogFilePicker to choose files of the various types. It didn't seem like the original routine was reaching SelectFile. Nothing like Worksheet_Change, or Workbook_Activate.

Public Function SelectFile(FilePrompt, FileType As String)

Set myFile = Application.FileDialog(msoFileDialogFilePicker)
        With myFile
            .Title = "Select File..."
            .AllowMultiSelect = False
            .InitialFileName = initFolder
            .Filters.Clear
            .Filters.Add "All Files", FileType, 1
            If .Show <> -1 Then
                Exit Function
            End If
            SelectFile = .SelectedItems(1)
        End With
                
End Function

 

[THNDRacket]

Well, thanks AlphaFrog! I put those two EnableEvents lines in, and it seemed to work. I will investigate further...

Initially I had ThisWorkbook.Sheets("sheet1").Activate after the MsgBox, but when it started looping, I moved it before, and it still didn't remedy it.

 

[AlphaFrog]

Well, thanks AlphaFrog! I put those two EnableEvents lines in, and it seemed to work. I will investigate further...

Initially I had ThisWorkbook.Sheets("sheet1").Activate after the MsgBox, but when it started looping, I moved it before, and it still didn't remedy it.

Check both the Sheet1 code module and the Thisworkbook code module.

 

[THNDRacket]

Oh I see. Yes, in the Sheet1 module, there was a Worksheet_Change calling the subroutine. I had initially set it up using data validation in the cell and decided to go with a prompt. I guess that is where it came from.