About these examples...
On this page we offer some free examples of how to use VBA with Microsoft Excel to produce tools that will make your use of Excel more efficient and simpler. There are also some useful tools in the form of Microsoft Excel addins that we actually use on a daily basis. These are offered free of charge Please note that the examples are all zipped so you will possibly need an unzip program such as Winzip
Note: Click on the images to enlarge
J & R Database Form...
This has been one of our most popular downloads
for some time. It demonstrates how to create a UserForm in Visual Basic for
Applications to simplify the entry of data into a table of a Spreadsheet. It can
also be used to search for an item and will create a list of all items
found. the
The zipped file now contins two examples, one is similar to the original but I
have updated the code. Also, there are now two OptionButtons on the form and
sample code demonstrating how to use them. Also, the controls are re-set during
use of the form by looping through the controls.
The second example demonstrates how to load an image associated with the record onto the Form. It uses an ImageControl. All images should be stored in a sub folder called images. The example zip file contains some images for trial use, simply extract the folders to your computer.
Navigating a large workbook...
Often a Spreadsheet can contain many sheets and can become difficult to navigate, many of the Navigation Tabs being out of sight. You can of course right click on the navigation arrows at the bottom left of the Workbook and choose "More sheets..." Here we offer some suggestions to overcome this using Microsoft Visual Basic for Applications and at the same time make your Workbook a little more professional, in fact you could even hide the sheet tabs if you wish. use the Tools menu and select Options and in the View tab de-select Sheet Tabs.
The first method creates a Table of Contents in a separate sheet. This page will contain hyperlinks to all the sheets in the Workbook so that a simple click will open the required sheet. You simply need to copy & paste the code into your own Workbook, alternatively if you have the sample Workbook & your Workbook open you can simply "drag & drop" the code into the destination. Download the working example here, you could even use this as a Template for future work. If you don't want to do this we also have a free addin to do the work for you, get it here.
For a really professional tool you could install our Navigation Toolbar, this works in any Workbook and updates as you switch from Workbook to Workbook, or add and delete sheets. Click here to for more information
Contacts Form...
This example demonstrates how a simple form can be created on a spreadsheet to save data to another sheet that acts as a database. The data entry cells in the example are on a separate sheet, but there is no reason why they cannot be placed above the table of data actually on the database Sheet.
This example demonstrates the use of Offset to enter the data, .End(xlUp) to locate the next empty Row for Data input, a MessageBox to inform the user of successful completion and also how to hide unnecessary Columns and Rows using the Format Menu.
Click here to download the example
Excel Web Toolbar
This is an Excel addin that enables you to quickly log on to your Favourite Excel Web Sites & Forums. There is a even a Search function. Simply type your query into the TextBox and select then Forum of your choice or Google.
Click to enlarge
The addin is downloaded as an .exe installation file. When the installation is complete a GUI is displayed which will help you correctly install the addin.
After installation, you will find a new item in your Excel Tools Menu - Excel WebToolBar. From this item you can launch the ToolBar or even disable or completely uninstall it.
Click here to download the addin
Hyperlink in a UserForm
This example demonstrates how to use a label on UserForm to simulate a hyperlink The form example provides a quick and easy way to let users email you directly from the UserForm. This code allows users to click on a label that contains an email address, and open the default email application and fills in the email address All the user has to do is fill in the subject, and the comments they wish to send. The other labels act as hyperlinks to websites.
This workbook demonstrates the use of the Shell Command, formatting Labels on a UserForm, sending an email from Microsoft Excel. It also shows how to use the WorkSheet_Change event to call the UserForm, this can be adapted to trigger any Macro.
This example can be useful if you want to include a "Contact us" form in your applications.
Click here to download the example
Message Box Example
This is a simple example to get a user's confirmation with a MessageBox. This feature is very useful and can be used in numerous ways which we will demonstrate later in one of our planned Tutorials.
Click here to download the example
Populate UserForm
This example demonstrates how to call a UserForm by using the Before_DoubleClick event, and to use the UserForm_Initialise event to populate Textboxes. It would be simple o adapt this example by adding Commandbuttons to add, delete or even amend data.
Click here to download the example (47Kb).
Worksheet Protection with UserInterFace Only...
Individual worksheets can be protected using VBA, the only problem is that you will need to write an unprotect routine into your code to allow user input via your code. The best way around this is to setup a blanket protection scheme for every sheet in the workbook but still allow all your macros to function without interference from the protection. This is done when the workbook is opened. You must protect your VBA project as well. To do this, whilst in the VB Editor open the Tools menu and select VBAProject properties, then check Lock project for viewing and finally type in and confirm your password. To be effective you must then close & save your workbook. See this demo.
Jf you need to exclude specific sheets then adapt this code
Const PW As String = "password"
Dim wSht As Worksheet
Application.ScreenUpdating = False
For Each wSht In Worksheets
Select Case wSht.Name
'exclude these sheets, amend names and add others if required
Case "Sheet1", "Sheet2"
Case Else
With wSht
wSht.Protect _
Password:=PW, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End With
End Select
Next wSht
Application.ScreenUpdating = True
Click
here to download the example (47Kb).
Always remember that. although the Visual Basic password is stronger than Excel's worksheet protection, it can still be cracked. If your projection needs stronger protection check this.
Adding a Menu or Toolbar to access your Macros
If you have built a collection of macros that you use regularly on different workbooks then the best way to tore them is as an Excel addin (*.xla). The easiest way to access the macros is to add a custom menu or Toolbar to your addin. Here's a couple of examples:
Note if using Excel 2007 then it's better to add to the Ribbon although you can access Toolbars & menus in the addins Tab
Note: all our examples are offered as is with no warranty. J & R Excel Solutions accept no responsibility for any loss arising from your use of them