none
How to automatically Confirm "Yes" if a message Box appears while refreshing the TFS Query in MS-Excel RRS feed

  • Question

  • Hi,

    I am writing a Excel VBA to automatically refresh the TFS query.

    After the statement :  refreshButton.Execute    , It pop up occurs asking Are you sure to refresh.

    I want it to automatically confirm the pop up as "YES" and refresh the sheet(query).

    I have tried using    Application.DisplayAlerts = False &   Application.DisplayAlerts = True. But, it didn't work.

    Snapshot Link : social.msdn.microsoft.com/Forums/getfile/814239. Add https://

    Thanks & Regards,

    Archit

     Sub CommandButton1_Click()
    
    
    RefreshAllTeamFoundationListObjects
    End Sub
    
    
    
    Public Sub RefreshAllTeamFoundationListObjects()
    
    
       '  ‘Turn off updating the UI so we don’t get a lot of flicker
    
        Application.ScreenUpdating = False
    
    
      '  ‘Make sure we turn screen updating back on if we get any errors
    
        On Error GoTo ErrorHandler
    
    
       ' ‘Save the current worksheet and selection range
    
        Dim curWs As Worksheet
    
        Set curWs = ThisWorkbook.activeSheet
    
    
        Dim curSel As Range
    
        Set curSel = Application.Selection
    
    
       ' ‘Iterate over all worksheets in the current workbook
    
        Dim ws As Worksheet
    
    
        For Each ws In ThisWorkbook.Worksheets
    
            If ws.ProtectContents Then
    
                MsgBox "Skipping protected worksheet " + ws.Name
    
            End If
    
    
           ' ‘Activate the worksheet or the selection change (below)
    
            '‘will Not work
    
            ws.Activate
    
    
         '   ‘Iterate over all listobjects on the worksheet
    
            Dim list As ListObject
    
    
            For Each list In ws.ListObjects
    
                Call PerformRefresh(list)
    
            Next
    
        Next
    
    
        '‘Restore the originally selected worksheet and selected range
    
        curWs.Activate
    
        curSel.Select
    
        Application.ScreenUpdating = True
    
    
        GoTo Success
    
    
    ErrorHandler:
    
        Application.ScreenUpdating = True
    
        MsgBox "An error occurred while refreshing the Team Foundation lists"
    
    
    Success:
    
        Application.StatusBar = "Finished refreshing Team Foundation Lists"
    
    
    End Sub
    
    
    Private Function PerformRefresh(list As ListObject)
    
       ' ‘See if this is a Team Foundation list.  Note:  ‘ELead’ string may change to ‘VSTS’ in the final release!
    
        If Left(list.Name, 4) = "VSTS" Then
    
          '  ‘Find the ‘refresh’ button.
    
            Dim refreshButton As CommandBarControl
    
            Set refreshButton = Application.CommandBars.FindControl(Tag:="IDC_REFRESH")
    
    
            'Save the current worksheet’s selection
    
            Dim wsSel As Range
    
            Set wsSel = Application.Selection
    
    
            list.Range.Select
    
    
         '   ‘Seems that sometimes it takes a little while for the button
    
    '        ‘to become enabled.  I’ve never seen it take more than 2 seconds,
    
     '       ‘though on slower machines it may take longer…
    
            For i = 1 To 10
    
                If Not refreshButton.Enabled Then
    
                    'Wait for the UI to settle down to ensure that the toolbar button has
    
                    'had time to be enabled
    
                    Application.Wait (Now + TimeValue("0:00:01"))
    
                    DoEvents
    
                End If
    
            Next
    
    
            If refreshButton.Enabled Then
                Application.DisplayAlerts = False
                refreshButton.Execute
          
                Application.DisplayAlerts = True
    
            Else
    
                MsgBox "Refresh button not enabled"
                
    
            End If
    
    
            'Restore the selection on the worksheet
    
            wsSel.Select
    
        End If
    
    
    End Function
    
    
    
    


    Wednesday, March 9, 2016 1:35 PM

Answers

  • Hi Archit,

    >>Is there any way to suppress that dialog Box ?

    If you change something in the list, it is impossible with Excel Object model. If this is a feature you want to include in the future version, I suggest you submit a feedback in the link below:

    https://excel.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, March 14, 2016 2:42 AM

All replies

  • Hi Archit,

    >> How to automatically Confirm "Yes" if a message Box appears while refreshing the TFS Query in MS-Excel

    As my test, I think it is impossible, and I would not suggest you do this even through there is one way to achieve your requirement.

    In my option, if user changes anything in the list, it will depend on user whether to submit new data or ignore the new data which entered in list.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 10, 2016 7:40 AM
  • Hi Edward

    Thanks for reply

    But,  I want the list to refresh automatically so that the new data list occurs .

    Is there any way to suppress that dialog Box ?

    Thanks

    Archit

    Sunday, March 13, 2016 6:52 PM
  • Hi Archit,

    >>Is there any way to suppress that dialog Box ?

    If you change something in the list, it is impossible with Excel Object model. If this is a feature you want to include in the future version, I suggest you submit a feedback in the link below:

    https://excel.uservoice.com/

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, March 14, 2016 2:42 AM