There are different types of popups and alerts available in office applications. In this post we will mostly focus over Excel Application. Alert windows are subject to developers’ need. Sometimes they get called based on need or sometime they must be suppressed. It is simply programming need. Let’s have a look some alters in Excel:
MSGBOX() : A very basic dialog which allows developer to report and error or message to the user with respect to the actions. This method exposes Show() method which is responsible to bring UI on the screen.
InputBox() : Displays a dialog box for user input. Returns the information entered in the dialog box. For details please visit previous post InputBox VBA.
Alert Category: The alerts can be classified into two categories:
- Application : Pops-up alerts at application level
- Worksheet : Pops-up alerts at worksheet level
Application Level alerts
DisplayAlerts : It is Boolean alert which takes True or False, this property suppresses any kind of dialog if set to False. Developer must set it back to True otherwise all prompts will be suppressed during application session.
Public Sub DisplayAlertTest() On Error GoTo errh Application.DisplayAlerts = False 'write your code here... Application.DisplayAlerts = True errh: If Err.Number <> 0 Then 'Set alerts property back TRUE in case of error Application.DisplayAlerts = True End If End Sub
CheckCompatibility: This property is kept at Workbook level hence. This property ensures compatibility dialog suppress action. Check compatibility dialog mostly comes due to office version incompatibility.
Public Sub CheckCompatibilityExample() On Error GoTo errh ActiveWorkbook.CheckCompatibility = False 'write your code here... Application.CheckCompatibility = True errh: If Err.Number <> 0 Then 'Set alerts property back TRUE in case of error Application.CheckCompatibility = True End If End Sub
CutCopyMode: This property usually used to flush clipboard memory during Copy/Paste operations in Excel.
Public Sub CutCopyModeExample() On Error GoTo errh Dim rng As Range Set rng = ActiveSheet.Range("A1:C20") rng.Copy ActiveSheet.Range("D1").Select ActiveSheet.Paste Application.CutCopyMode = False errh: If Err.Number <> 0 Then 'Catch error here End If End Sub
ScreenUpdating: This property is very useful when developer is performing bulk operations in a single action like moving data from one sheet to another sheet then building a table then coping the table and pasting it to another location etc.
Public Sub ScreenUpdatingExample() On Error GoTo errh Application.ScreenUpdating = False Dim rng As Range Set rng = ActiveSheet.Range("A1:C20") rng.Copy ActiveSheet.Range("D1").Select ActiveSheet.Paste Application.CutCopyMode = False Application.ScreenUpdating = True errh: If Err.Number <> 0 Then 'Catch error here Application.ScreenUpdating = True End If End Sub
EnableEvents: Excel provides event driven environment like Cell Change, Sheet Selection Change, Sheet Activate, Sheet Before Right Click etc. These actions are triggered automatic when excel encounters respective action and cannot be controlled. But some time Developer might want to skip actions and force excel to perform his code. The EnableEvents Boolean property helps developer to control the same.
Public Sub EnableEventExample() On Error GoTo errh Application.EnableEvents = False Dim rng As Range Set rng = ActiveSheet.Range("A1:C20") rng.Copy ActiveSheet.Range("D1").Select ActiveSheet.Paste Application.CutCopyMode = False Application.EnableEvents = True errh: If Err.Number <> 0 Then 'Catch error here Application.EnableEvents = True End If End Sub