Excel VBA Macro crashes when I select a New Sheet after calling an Excel Add In from VBA

[mcspamj]

Hello,

I have just written a series of VBA macros, one of which involves function calls from an Excel Add In.

As I don't want the Add In to be available all the time when I am in Excel, My VBA code switches the Excel Add In on using the code
AddIns("X").Installed = True followed by
Application.Run "Y" to call up a menu bar.
Once it has been utilized, I have then inserted another piece of VBA code to switch it back off again.


When I run the relevant macros, the Excel Add In pops up and Excel asks me to connect to an external server (from which a special excel function within the add in allows me to pull in data from an external database) :- So far, so good !

My next line of code then tries to select a different sheet in the macro enabled workbook (I am using Excel 2010) at which point it crashes.

If I go into the VBA debugger and place the cursor in the relevant sheet at the appropriate data line and press resume from within the debugger, the macro then continue to work fine, as originally intended.

Does anyone know of a way that I can avoid having this "hiccup" in the middle of my VBA macro, so that it runs smoothly without the need for manual intervention ?

Best Regards,
mcspamj

 

[MARK858]

A bit hard to tell without seeing your code or the addin but have you tried putting in the line

DoEvents

before you try changing Sheets?

 

[mcspamj]

Hello mark858,

I think this function may be what I am looking for, but am not sure how to use it !?

The problem seems to be that when the add in is installed into the spreadsheet by my VBA code, it takes some prcessing time before excel connects via the add in funtion to the external server on which the database resides.

In the meantime, the VBA code races ahead to try and process the next bit of code, which is to go to the specific worksheet.

I tried typing in "Doevents" immediately after the add in has been installed and the menu to called up to see if it would pause the processing until excel had connected to the server. However, no luck, same error message !

Is there another way I can pause processing the VBA code, but still allow Excel to reach out and connect to the external server ?


Best regards,
mcspamj

 

[MARK858]

Post your code and your url so we can see exactly what is happening

 

[mcspamj]

Hello,

The VBA code is as follows :-
'Run each macro in sequence
Application.Run "ImportDownload"
'Ask the user whether they want to connect to Pricemart ? - If not exit the remaining macros
ConnecttoPricemart = MsgBox("Do you want to connect to Pricemart ?", vbYesNo, "Connect to Pricemart")
If ConnecttoPricemart = vbYes Then
'Pull In Pricemart Excel Add In
AddIns("Pricemart").Installed = True
Application.Run "AddPriceMartMenuBar"
ElseIf ConnecttoPricemart = vbNo Then
Exit Sub
End If

DoEvents

'Ask the user whether they want to reformat Sales Data ? - If not exit the remaining macros
ReadytoReformat = MsgBox("Are you ready to reformat Pricing data ?", vbYesNo, "Reformat Sales Data")
If ConnecttoPricemart = vbYes Then
'Run Reformat Detailed Invoices macro
Application.Run "ReformatDetailedInvoices"
ElseIf ConnecttoPricemart = vbNo Then
Exit Sub
End If

Application.Run "InvoiceSummary"

When the second question is answered yes, macro ReformatDetailedInvoices starts to run. The first VBA command is Sheets("Detailed Invoices").Select and when VBA tries to execute this command the debug screen comes up. If you move to the above sheet and press the resume button, the macro starts to run again as it should.

Best Regards,
mcspamj