Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

Flashing Formula Excel - Caused by VBA?

I am working on a project with many userforms with a wide range of controls (listboxes, textboxes, MonthView etc.) using Windows 10.

Once data has been added to the worksheet, the formula bar keeps flashing. This also happens when I add data from a userform which doesn't have the standard controls.

The worksheet does not have any shapes: just listboxes, charts and pivot tables. Formulae are kept to a minimum as the logic is implemented in the userform. I decided to use this method as I have thousands of entries in various tables.

What are the likely causes of the formula bar flashing?
Has anyone experienced the same problem in the past?
0
Massimo Scola
Asked:
Massimo Scola
  • 4
  • 3
1 Solution
 
Ryan ChongCommented:
>>What are the likely causes of the formula bar flashing?
you may try to turn off the Excel screen updates by using command:
Application.ScreenUpdating = False

Open in new window


Application.ScreenUpdating Property (Excel)
https://msdn.microsoft.com/en-us/library/office/ff193498.aspx
0
 
Massimo ScolaAuthor Commented:
Isn't the screen updating command for actions that take place on the worksheet? (Not formula bar)
0
 
Ryan ChongCommented:
do you have a sample can share with us? we will try to diagnose the issue
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Massimo ScolaAuthor Commented:
How confidential will the data be treated which I upload here (as part of private question)?
0
 
Ryan ChongCommented:
>>How confidential will the data be treated which I upload here (as part of private question)?
definitely knowing your concern but without knowing the real scenario, it's difficult for troubleshooting.

you can try to make a copy, and then delete/mask your data if it's feasible to upload.
0
 
Massimo ScolaAuthor Commented:
Before upload the workbook, let me show you one more thing.
I use these subs to turn on the speed or off:

Sub SpeedOn()
DoEvents
    On Error Resume Next
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Cursor = xlWait
        .EnableCancelKey = xlErrorHandler
    End With
End Sub

Sub SpeedOff()
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .CalculateBeforeSave = True
        .Cursor = xlDefault
        .EnableCancelKey = xlInterrupt
    End With
End Sub

Open in new window


Is this one way to speed on/off ?

I usually add SpeedOn before saving data to the workbook and SpeedOff after saving the data.
0
 
Massimo ScolaAuthor Commented:
I have absolutely no idea what happened but for some reason the worksheet now works.

I hope this won't happen again.

Thanks for your help.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now