Excel VBA: Create Masked Password Dialog

Want to make a masked password dialog for your workbook?

Check this tip from Microsoft.

VBA: Break All External Links in a Workbook

Here's a tip how to break all external links from a workbook:



Sub BreakAllLinks()


    Dim arrStrLinks As Variant
    
    ' Read workbook links to an array.
    arrStrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    
    ' Loop through whole array -> all links.
    For i = 1 To UBound(arrStrLinks)
        ActiveWorkbook.BreakLink _
        Name:=arrStrLinks(i), _
        Type:=xlLinkTypeExcelLinks
    Next i


End Sub


This is actually a slightly modified version of the one you can find from Excel's help with keyword BreakLinks.

VBA: Remove All Shapes From Sheet

To remove all shapes from a sheet, use the below snippet:

Sub RemoveShapes()


    Dim s As Shape


    For Each s In ActiveSheet.Shapes
        s.Delete
    Next s


End Sub

VBA: Deleting Rows Programmatically

When you need to delete rows in your spreadsheet programmatically, start from the last row and head towards the first one. This can be accomplished by using Step -1 in the For loop, see example below:


For lRow = lLastRow To lFirstRow Step -1
    ActiveSheet.Rows(lRow).Delete
Next lRow


If we would loop starting from the first row, our lRow counter would be pointing incorrectly at the moment when a first row gets deleted.

Limit the number of times a file can be opened

Excellent tip on Excel Blog: VBA tip: Limit the number of times a file can be opened

Article author Bob Umlas (Excel MVP) shows a convenient usage of SaveSetting and GetSetting methods, take a look!

Excel VBA: How to Break Links to External Workbooks

Back in 2007 one of the prettiest pieces of VBA code was given free to the people. If you have ever needed a good programmatic way to remove all external workbook links from a single workbook, check this link.

Excel VBA: Remove All Conditional Formatting From Sheet

At times you may experience issues with macros when having lots of conditional formatting rules on a sheet. Here's the magic with which you can remove all conditional formatting rules from a sheet:

Sheets("MySheet").Cells.FormatConditions.Delete

Excel VBA: Does the Cell or Range Have a Formula

Need to quicky check if a cell contains a formula? Use Range's HasFormula property:

Property definition

expression.HasFormula

• Expression = Range object
• Returns:
• True if all cells in range contain a formulss
• False if none contain formulas
• Null if some cells contain a formula 
• Read-only Variant

Use IsNull to check if the result is Null.

Example 1

Scenario
You have a column, where you want the user to have two options:

1. By default include a formula
2. Let the user enter a value manually

The formula is updated on Worksheet_Open or a button.

Solution 

' 

' Update only cells with formulas.

'

If ActiveSheet.Cells(Row, Column).HasFormula  Then

    ' Update or refresh your formula here.

End If

Example 2

See MSDN's HasFormula Property page for an example which recalculates the active worksheet if any cell in the currently selected range contains a formula.

Excel Error: Error 800a0011 When Trying to Add a Control to UserForm

When adding a form control to a UserForm you might get the following error message:

Excel could not complete the operation due to error 800a0011

When this occurs, your VBA code is most likely currently in break mode.

Click OK in the error dialog and reset your VBA Editor (Run->Reset). Now you can add the form control to your UserForm.

Works at least for Excel 2007.