How to disable YES or No Dialog Box in Excel VBA

[archit013]

Hi,
I was trying to automate the tfs refresh in excel. I wrote the VBA script but when I run it, It always ask whether to refresh or not. I want to disable this.
Thanks for help
Archit

 

[John Jairo V]

Hi!
If you mean to disable warning messages in Excel, you can try :

At the start of code:
application.displayalerts = False

At the end of code:
application.displayalerts = True

Blessings!

 

[archit013]

I tried this but didn't work.
Still It asks Whether you want to replace the work items or Not. Yes or No.

 

[John Jairo V]

Is not clear that you want (at least for me). Please upload a sample excel file and show us what you have and what you want. Blessings!

 

[archit013]

Actually, When you try to refresh Team foundation server from Team Ribbon, then it will be ask you whether you want to replace your all work items or not.
You need to click Yes Or No. If I try this refresh from automation, it will again pop up asking same. So, I want it to automatically Take "YES".


Thanks
Archit

 

[archit013]

Any help ?

 

[Debaser]

I suspect that the dialog is provided by the TFS add-in's code so will not be affected by DisplayAlerts. It might be possible to set a CBT hook and dismiss the dialog that way, but I cannot test since I don't use TFS at all.

 

[archit013]

Yes, that dialog is by TFS. Can you brief about CBT hook method ?

 

[Debaser]

There's an example of the sort of API calls you need here: http://vb.mvps.org/samples/MovedMsg/

 

[shrivallabha]

Interesting situation. I have never personally dealt with such situation. There's a built in object in MS Word which can close task.

Could you try below method and see if it works for you?

1. Paste below code in a text file and save it as CloseTFSdialog.vbs (you can assign any other name). Make sure you find the exact task name.

Dim wdApp
Dim tsk
Dim blfinished
Set wdApp = CreateObject("Word.Application")
wdapp.Visible = False
blfinished = False
do while blfinished <> true
wscript.sleep 1000
For each tsk in wdApp.Tasks
'\\ Get the exact task name of tfsdialog first
    If Instr(tsk.Name,"tfsdialog") > 0 then
        tsk.close
        blfinished = true
    end if
Next
Loop
wdApp.Quit
Set wdApp = Nothing

2. Place call for above vbscript in your VBA code before line which invokes the tfs dialog. Code like below can be used for invoking it.
http://stackoverflow.com/questions/13133126/calling-a-vbs-script-from-excel-vba

3. VBScript will keep running until the tfsdialog opens and then it will close it using task name.

 

[archit013]

No, it didn't work.
In other way, I want that dialog box to automatically confirm "Yes" and refresh the sheet .

Thanks
Archit

 

[shrivallabha]

OK. Try following VBScript. I am assuming that the default focus button is "OK" on the tfs dialog. If not, then we may have to tweak.

Dim wdApp
Dim wShell
Dim tsk
Dim blfinished
Set wdApp = CreateObject("Word.Application")
Set wShell = CreateObject("WScript.Shell")
wdapp.Visible = False
blfinished = False
do while blfinished <> true
    wscript.sleep 1000
    For each tsk in wdApp.Tasks
    '\\ Get the exact task name of tfsdialog first
        If Instr(tsk.Name,"Excel") > 0 then
            tsk.Activate
            wShell.SendKeys "{ENTER}"
            blfinished = true
        end if
    Next
Loop
wdApp.Quit
set wShell = Nothing
Set wdApp = Nothing

 

[archit013]

Now, there is an error : Cannot activate application.
It is generated by Windows Script Host.

If Instr(tsk.Name,"Excel") > 0 then
tsk.Activate

Line 14

And, what would be the task name ?

 

[shrivallabha]

Hard to guess. Maybe it is not allowing it activate in case of tfs. What did you use earlier? Did that work? If yes, then please use the same

'\\ Get the exact task name of tfsdialog first
    If Instr(tsk.Name,"tfsdialog") > 0 then
tsk.activate
wShell.SendKeys "{ENTER}"
end if

 

[archit013]

No, it didn't work earlier too. But, there was no error.
I tried writing whole msg too but didn't work.

 

[shrivallabha]

Open empty word document and then open visual basic editor. Paste the following code:

Public Sub RunningTasks()
Dim tsk As Task
Dim strTaskList As String
For Each tsk In Application.Tasks
    If tsk.Visible Then
        strTaskList = strTaskList & vbCrLf & tsk.Name & "|" & tsk.Application & "|" & tsk.Parent
    End If
Next
ActiveDocument.Range.Text = strTaskList
End Sub

It will generate a list of running tasks in the word document. See if you can locate the item which you are looking for.