Refresh All Data Connections on Workbook Open

Your workbook may have connections to external data sources such as web queries, MSQuery connections, PivotTable connections, and so on. In these cases, it may be helpful to refresh all data connections automatically when the workbook is opened. The following macro code allows you to refresh all data connections on…

Determine If a File Exists in a Directory

Sometimes you may need to determine if a file exists in a directory. For instance, the purpose of your macro may be to open and read from a text file. Before doing so, you will want to check to see if the file exists, in order to avoid an error….

Determine If a Excel Workbook is Already Open

The last macro automatically opened a specific workbook chosen by the user’s selection. As we consider automatically opening workbooks, you must think about what may happen if you attempt to open a workbook that is already open. In the non-VBA world, Excel attempts to open the workbook again, with a…

Opening a Specific Workbook Chosen by the user

Sometimes you need to allow your users a rapid way to open a Excel Workbook? This macro opens a friendly dialog box, allowing you to browse for and open the Excel Workbook of your choosing. Macro Code Sub OpenSpecificFile() ‘Step 1: Define a string variable. Dim SpecificFileName As Variant ‘Step…

Open a Workbook to a Specific Sheet

Sometimes you need to open your workbook be started on a specific worksheet. With this macro, if a user is working with your workbook, they can’t go astray because the workbook starts on the exact worksheet it needs to. Macro Code Private Sub Workbook_Open() ‘Step 1: Select the specified sheet…

Unprotect a Worksheet on Workbook Open

Sometimes you need to unprotect the worksheets in a workbook before continuing your work. If you find that you’re constantly unprotecting worksheets, this macro maybe help you. Macro Code Private Sub Workbook_Open() ‘Step 1: unprotect the sheet with a password Sheets(“Sheet1″).Unprotect Password:=”ExcelHowTo” End Sub How This Macro Works This code…

Protect a Worksheet Before Workbook Close

Sometimes you need to send your workbook out into the world with specific worksheets protected. If you find that you’re constantly protecting and unprotecting sheets before distributing your work-books, this macro can help you. Macro Code Private Sub Workbook_BeforeClose(Cancel As Boolean) ‘Step 1: Protect the sheet with a password Sheets(“Sheet1”).Protect…

Auto Save a Workbook Before Closing

This macro is an excellent way to protect users from inadvertently closing their file before saving. When implemented, this macro ensures that Excel automatically saves before closing the workbook. Excel normally warns users who are attempting to close an unsaved workbook, giving them an option to save before closing. However,…