Various Popups and Alerts Dialogs Excel Add-in code example

Popups

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:

Popup Category

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.

Code example

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.

Code example

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.

Code example

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.

Code example

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.

Code example

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

Next>> List of Excel MenuBar commands and IDs

Share on Facebook

Tweet