Application.ScreenUpdating = False does not work

You need to either restore the control tab before turning screen updating back on, or not activate the other tabs in the first place.

Kevin

zorvek,

I need to activate the tabs, because I need to access their command bars in order to refresh the data on the report. Even if I  restore the control tab before turning screen updating back on, wouldn't there still be a flash to the report tab while it's activated?

taduh

Instead of Application.ScreenUpdating you might Try DoCmd.Echo

DoCmd.Echo False   'Updates still happen, it just doesn't show them  '
DoCmd.Echo True    'Any updates previously done are now visible.     '

Select allOpen in new window

If you leave screen updating off until you after you have restored the control tab you should not see any flashing of undesired content.

Rick, DoCmd.Echo is an Access command, it doesn't work in Excel.

Kevin

You don't need to activate the tabs or access their command bars to refresh data. There's always a object method you can call that does not require and form of screen updating. Let us see more of your code, including the areas that you believe require activating new sheets, and maybe we have a workaround.

You should never have to make a call to things like .activate and .select when automating. Those are things that excel records in macros, but that can always be circumvented.

Alain

zorvek,

So what you are saying is to set screenupdating to false, then run my report sub, then restore the control tab, the tset secreenupdating to true. Is this correct?

taduh

Correct.

Kevin

Zorvek,
 
It still flashed.

alainbryden,

That's a legitimate concern, but do you know how I can get information on the methods in the rhxl32 add-in?

Is this the Hyperion Database addin?

Yes

Let me clarify - its the Hyperion Enterprise database - not Essbase.

You can't avoid flashing altogether, but if you leave screen updating off until after you have restored the control tab, it should be minimal.

In your code you have this line at the end of the report routine:

application.ScreenUpdating = True

That will cause a flash of the hidden tab.

Kevin

For the benefit of others viewing the question:

http://www.controller.iastate.edu/controller/hypdoc.pdf (Page 15+)

hyperion.PNG

I see you've already attempted to travel down this path: https://www.experts-exchange.com/questions/23548176/Looking-for-a-way-to-invoke-Hyperion-Enterprise-drop-downs-through-Visual-Basic-for-use-in-Excel-spreadsheet.html

Zorvek,

So there's no way that you know of to prevent the flashing?


alainbryden,

Do you know any methods for RHXL32?

taduh

I guess if in enterprise there's no way to automate it, then you're stuck with this. You can't really dig into their protected addin methods. If they didn't provide a way to run their code through automation then you'll have to simulate the UI interactions.

Go with Kevin's solution and live with the flashing I guess. If you disable screen updating other than when you're activating new sheets, it will minimize the flashing like Kevin said.

I recommend that instead of referring to control 11 (since in the above example it's control 9) you should iterate through all the controls first, find the one with the name "RH&XL", and then call the first command.

alainbryden,

I tried this to check the control, but it errors out:

If application.CommandBars(1).Controls(11).Name = "RH&XL" Then

Any ideas as to the appropriate syntax?

taduh

Got it. It's this:

If application.CommandBars(1).Controls(11).Caption = " RH&XL" Then

One other question/comment: Why a command like application.screenupdating = False provided, if when you activate a page, its going to ignore the screenupdating setting?

taduh

Well I'm pretty sure it's .Text or .Caption, not .Name

And yeah, if you only see the screen flash once, then you've won - because that's the minimum for turning screen updating off then on again as Kevin said. Even if you are seeing a quick flash of the other worksheet, there's not much you can do there since you're activating it.

>Why a command like application.screenupdating = False provided, if when you activate a page, its going to ignore the screenupdating setting?

It should not be ignored. Make sure you have removed all occurrences of Application.ScreenUpdating = True in all called routines.

Kevin

Also, just for the sake of mentioning it - realize that screenUpdating = False has no effect if you are stepping through (debugging) your code...

alainbryden,

I figured that was the case. Okay, based on your recommendation, I have set up logic to check for the RHXL add-in and to note the position of it on the Control bar.

One other question, related to flashing and I thought I had posted this earlier, but I don't see it in the postings. When I hide report sheets, it stops the flashing,  but it causes an error which previously did not exist, in the subroutine where I print reports to PDF. The code's below: It errors out in the Else portion. The error message is Run-time error '1004': Method 'Range' of object '_Global' failed.  

If ActiveSheet.Name = "TN Graphs" Then
        ActiveChart.PrintOut Copies:=1, _
        Preview:=False, PrintToFile:=True, Collate:=True, _
        ActivePrinter:="Acrobat Distiller", _
        prtofilename:=PSFileName
    Else
        Range(ActiveSheet.PageSetup.PrintArea).PrintOut Copies:=1, _
        Preview:=False, PrintToFile:=True, Collate:=True, _
        ActivePrinter:="Acrobat Distiller", _
        prtofilename:=PSFileName
    End If
       
Once again, this message did not appear until I hid the report pages.

Thanks,

taduh

replace
Range(ActiveSheet.PageSetup.PrintArea).PrintOut
with
Worksheets("MyWorksheetName").Range(Worksheets("MyWorksheetName").PageSetup.PrintArea).PrintOut

Same thing with activesheet, make it Worksheets("MyWorksheetName")

You should never depend on the implicit form of referencing a range or cell property value

Well I guess if you don't want to mess with the way things are currently going, you might even solve your problem just by writing
ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea).PrintOut Copies:=1, _ ...

But like I said, it's awkward using code that refers to the active sheet instead of a sheet in particular or a nonstatic reference to a sheet object.

Alainbryden,

I used your second alternative because I'm calling the Print PDF subroutine to print a variety of different reports on different worksheets. Thats why I used the refernce to Active sheet.

Unfortunately, I got the following error:
"Application-defined or object-defined error"

Any suggestions?

taduh

you get that error on that line of code?

Maybe the code just isn't right. Try macro-recording the print action and see what code excel pops out.

I've decided to live with the flashing - there's no real way to stop it and it allows the user to see what's going on - Zorvek - 50 points for your time and effort - I appreciate it.

I've awarded 450 points to alainbryden because he noticed what could have been a real problem in my code - the controls on the commandbar could change or may be configured differently for different people who will use my application - and guided me through a way to determine if the controls I need even exist on the commandbar and if so, what control number they are. Kudos for preventing what could have been a big headache.

The error "Application-defined or object-defined error" would likely occur from the way the object is being passed.  

    Dim sht As Excel.Worksheet
    Set sht = Excel.ActiveSheet
    sht.Range(sht.PageSetup.PrintArea).PrintOut Copies:=1
    
'    or...
    
    Excel.ActiveSheet.Range(Excel.ActiveSheet.PageSetup.PrintArea).PrintOut Copies:=1
    
'    or if you're refering to an excel object it would look something like...
    
    objXL.ActiveSheet.Range(objXL.ActiveSheet.PageSetup.PrintArea).PrintOut Copies:=1

Select allOpen in new window